Home |
Search |
Today's Posts |
#1
|
|||
|
|||
VBA
I believe I need a VBA to do what I want. Since I'm not good at VBA, I'm
hoping someone out there can help me out. Here's what I have in Column A: 1)Rent Type 2)Date 3)Category 4) 5)Recourse 6)12/31/04 7)Net 8) 9)Non-Recourse 10) 6/30/05 11) Full I imported this info and what happened is that the program stacked 3 areas of info into one column. What I want to do is break that out into 3 separate columns. So Col A will be Rent, Col B will be Type and Col C will be Category. The next 3 rows is the associated info. I can breakout the column headings, but can a formula be written that will put what is in row 5 under column A, row 6 under col B and row 7 under col C. It would then skip Row 8, put what is in row 9 under col A, what is in row 10 under col B and what is in row 11 under col C. I have approximately 500 rows of data. Thanks! |
#2
|
|||
|
|||
Hi, Betty-
No VBA required: here's a formulaic workaround for you. Step 1: Make a backup copy of your data, and keep your original data intact! That way if something goes horribly wrong you can revert to your original information. Step 2: Insert a blank row at the beginning of your data. The mockup I created shows the Rent Type column header in A2, Date in A3, and Category in A4. The first group of sample data, Recourse, 12/31/04, and Net appear in A6, A7, and A8, etc. Step 3: Enter this formula in cell B2: =IF(A1="",A2,"") Enter this formula in C2: =IF(A1="",A3,"") Enter this formula in C3: =IF(A1="",A4,"") Copy these formulas, and paste them into all the rows of your spreadsheet. The formulas look for the blank row in between groups of data, and transpose them into rows. The result will show a series of blank rows, however; copy columns B, C, and D, then paste them back over themselves at values to convert them formulas into static values. Then delete the extra rows. |
#3
|
|||
|
|||
Betty,
Sub BettysMacro() Dim Rng As Range Dim Dest As Range Dim myArea As Range Dim i As Integer Set Rng = ActiveSheet.Range("A:A"). _ SpecialCells(xlCellTypeConstants, 23) For Each myArea In Rng.Areas Set Dest = ActiveSheet.Range("B65536").End(xlUp)(2) For i = 1 To myArea.Cells.Count Dest(1, i).Value = myArea(i, 1).Value Next i Next myArea ActiveSheet.Range("A:A").EntireColumn.Delete End Sub HTH, Bernie MS Excel MVP "Betty Csehi" wrote in message ... I believe I need a VBA to do what I want. Since I'm not good at VBA, I'm hoping someone out there can help me out. Here's what I have in Column A: 1)Rent Type 2)Date 3)Category 4) 5)Recourse 6)12/31/04 7)Net 8) 9)Non-Recourse 10) 6/30/05 11) Full I imported this info and what happened is that the program stacked 3 areas of info into one column. What I want to do is break that out into 3 separate columns. So Col A will be Rent, Col B will be Type and Col C will be Category. The next 3 rows is the associated info. I can breakout the column headings, but can a formula be written that will put what is in row 5 under column A, row 6 under col B and row 7 under col C. It would then skip Row 8, put what is in row 9 under col A, what is in row 10 under col B and what is in row 11 under col C. I have approximately 500 rows of data. Thanks! |
#4
|
|||
|
|||
Hi Betty,
VBA it is Sub MoveData() Dim iLastRow As Long Dim i As Long Dim iRow As Long iRow = 1 iLastRow = Cells(Rows.Count, "A").End(xlUp).Row For i = 1 To iLastRow Step 4 Cells(iRow, "A").Value = Cells(i, "A").Value Cells(iRow, "B").Value = Cells(i + 1, "A").Value Cells(iRow, "C").Value = Cells(i + 2, "A").Value iRow = iRow + 1 Next i Range(Cells(iRow, "A"), Cells(iLastRow, "A")).ClearContents End Sub -- HTH RP (remove nothere from the email address if mailing direct) "Betty Csehi" wrote in message ... I believe I need a VBA to do what I want. Since I'm not good at VBA, I'm hoping someone out there can help me out. Here's what I have in Column A: 1)Rent Type 2)Date 3)Category 4) 5)Recourse 6)12/31/04 7)Net 8) 9)Non-Recourse 10) 6/30/05 11) Full I imported this info and what happened is that the program stacked 3 areas of info into one column. What I want to do is break that out into 3 separate columns. So Col A will be Rent, Col B will be Type and Col C will be Category. The next 3 rows is the associated info. I can breakout the column headings, but can a formula be written that will put what is in row 5 under column A, row 6 under col B and row 7 under col C. It would then skip Row 8, put what is in row 9 under col A, what is in row 10 under col B and what is in row 11 under col C. I have approximately 500 rows of data. Thanks! |
#5
|
|||
|
|||
Thanks for the help and quick response - it is most appreciated!
"Betty Csehi" wrote in message ... I believe I need a VBA to do what I want. Since I'm not good at VBA, I'm hoping someone out there can help me out. Here's what I have in Column A: 1)Rent Type 2)Date 3)Category 4) 5)Recourse 6)12/31/04 7)Net 8) 9)Non-Recourse 10) 6/30/05 11) Full I imported this info and what happened is that the program stacked 3 areas of info into one column. What I want to do is break that out into 3 separate columns. So Col A will be Rent, Col B will be Type and Col C will be Category. The next 3 rows is the associated info. I can breakout the column headings, but can a formula be written that will put what is in row 5 under column A, row 6 under col B and row 7 under col C. It would then skip Row 8, put what is in row 9 under col A, what is in row 10 under col B and what is in row 11 under col C. I have approximately 500 rows of data. Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|