| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a multi dimensional array using transpose - Is this the best answer?
Hi,
I have imported a text file and am trying to break out this information into a format I can import into a database. I basically have addresses that can be either 3, 4, or 5 lines long. For example, here are 2 records: ACREE JEFFREY D & REBA D 15057 LOCUST POINT KING GEORGE VA 22485 ACREE PEARL (LIFE); ACREE DAV JOHNNY; JUDITH & GRIFFIN LINDA C/O JUDITH RAE ACREE 175-D ELK CREEK ROAD MOUNT CLARE WV 26408 I need to break out the names, address, city, state, and zip. I am assuming because of the make up of the file, I probably need to work backwards and fill in the zip first and work my way to the name to ensure the same type of data, ie State, goes into the same column. My thoughts were to use transpose to send each line to a new column and then split the cells to break out the city, state and zip, although they may be a problem with 2 named towns like those shown above. I was hoping for suggestions on the best way to do this before I start torturing myself. :) Thanks, Kim |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Creating a multi dimensional array using transpose - Is this the best answer?
Do you have a blank row between each record? If that is the case, it would be easy to put each row of one record in to different columns. BTW. I'm from WV too. -- a7n9 ------------------------------------------------------------------------ a7n9's Profile: http://www.excelforum.com/member.php...o&userid=32149 View this thread: http://www.excelforum.com/showthread...hreadid=562556 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Creating a multi dimensional array using transpose - Is this the best answer?
Hey fellow WVer! What part are you from? I do have a blank. This is
what I have right now: YOUNG AMELIA HC 33 BOX 2093 DORCAS WV 26847 YOUNG KENNETH K & LILA D RONALD YOUNG 19 MEADOW RIDGE PETERSBURG WV 26847 YOUNG RONALD LEE & SANDRA SUE 19 MEADOW RIDGE PETERSBURG WV 26847 YOWLER ROBERT C & KAREN A HC 75 BOX 109 NEW CREEK WV 26743 ZECK ELI & JOHN EDWARD ANNABELLE ZECK 1212 FLEMING AVE FAIRMONT WV 26554 ZELLMAN WILLIAM H JR & RUTH A 2909 PAPERMILL RD PHOENIX MD 21131 ZETAH VIRGINIA BERGESTON PO BOX 296 MAYSVILLE WV 26833 a7n9 wrote: Do you have a blank row between each record? If that is the case, it would be easy to put each row of one record in to different columns. BTW. I'm from WV too. -- a7n9 ------------------------------------------------------------------------ a7n9's Profile: http://www.excelforum.com/member.php...o&userid=32149 View this thread: http://www.excelforum.com/showthread...hreadid=562556 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Creating a multi dimensional array using transpose - Is this t
Sub ProcData()
dim rw as Long, rng as Range Dim sh2 as Worksheet, ar as Range rw = 1 With worksheets("Sheet1") set rng = .columns(1).Specialcells(xlblanks) End with set sh2 = Worksheets("Sheet2") for each ar in rng.Areas if ar.count = 4 then for i = 1 to 4 sh2.cells(rw,i).Value = ar(i) next elseif ar.cnt = 3 then for i = 1 to 3 sh2.cells(rw,i+1).value = ar(i) next rw = rw + 1 else msgbox ar(1).Row & " has a cnt of " & ar.rows.count end if Next ar End sub -- Regards, Tom Ogilvy " wrote: Hey fellow WVer! What part are you from? I do have a blank. This is what I have right now: YOUNG AMELIA HC 33 BOX 2093 DORCAS WV 26847 YOUNG KENNETH K & LILA D RONALD YOUNG 19 MEADOW RIDGE PETERSBURG WV 26847 YOUNG RONALD LEE & SANDRA SUE 19 MEADOW RIDGE PETERSBURG WV 26847 YOWLER ROBERT C & KAREN A HC 75 BOX 109 NEW CREEK WV 26743 ZECK ELI & JOHN EDWARD ANNABELLE ZECK 1212 FLEMING AVE FAIRMONT WV 26554 ZELLMAN WILLIAM H JR & RUTH A 2909 PAPERMILL RD PHOENIX MD 21131 ZETAH VIRGINIA BERGESTON PO BOX 296 MAYSVILLE WV 26833 a7n9 wrote: Do you have a blank row between each record? If that is the case, it would be easy to put each row of one record in to different columns. BTW. I'm from WV too. -- a7n9 ------------------------------------------------------------------------ a7n9's Profile: http://www.excelforum.com/member.php...o&userid=32149 View this thread: http://www.excelforum.com/showthread...hreadid=562556 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Creating a multi dimensional array using transpose - Is this t
Thanks Tom,
You want a job :). I'm only half joking if you do contracting work let me know. I haven't written code for 20 years but I'm the closest thing my boss has. Poor thing. I tried to run this and got an error on Sheet1 so I renaned the worksheet sheet1 and it got past that. Then it stopped on worksheet2 so I created that and it got past that. Now I am getting a Run time error '438' Object doesn't support thie property or method and it is pointing to this line of code: ElseIf ar.cnt = 3 Then Tom Ogilvy wrote: Sub ProcData() dim rw as Long, rng as Range Dim sh2 as Worksheet, ar as Range rw = 1 With worksheets("Sheet1") set rng = .columns(1).Specialcells(xlblanks) End with set sh2 = Worksheets("Sheet2") for each ar in rng.Areas if ar.count = 4 then for i = 1 to 4 sh2.cells(rw,i).Value = ar(i) next elseif ar.cnt = 3 then for i = 1 to 3 sh2.cells(rw,i+1).value = ar(i) next rw = rw + 1 else msgbox ar(1).Row & " has a cnt of " & ar.rows.count end if Next ar End sub -- Regards, Tom Ogilvy " wrote: Hey fellow WVer! What part are you from? I do have a blank. This is what I have right now: YOUNG AMELIA HC 33 BOX 2093 DORCAS WV 26847 YOUNG KENNETH K & LILA D RONALD YOUNG 19 MEADOW RIDGE PETERSBURG WV 26847 YOUNG RONALD LEE & SANDRA SUE 19 MEADOW RIDGE PETERSBURG WV 26847 YOWLER ROBERT C & KAREN A HC 75 BOX 109 NEW CREEK WV 26743 ZECK ELI & JOHN EDWARD ANNABELLE ZECK 1212 FLEMING AVE FAIRMONT WV 26554 ZELLMAN WILLIAM H JR & RUTH A 2909 PAPERMILL RD PHOENIX MD 21131 ZETAH VIRGINIA BERGESTON PO BOX 296 MAYSVILLE WV 26833 a7n9 wrote: Do you have a blank row between each record? If that is the case, it would be easy to put each row of one record in to different columns. BTW. I'm from WV too. -- a7n9 ------------------------------------------------------------------------ a7n9's Profile: http://www.excelforum.com/member.php...o&userid=32149 View this thread: http://www.excelforum.com/showthread...hreadid=562556 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Creating a multi dimensional array using transpose - Is this t
Hey Dave,
That did fix my runtime error. Now I get a message that reads "4 has a count of 1" I click OK and then it says "8 has a count of 1" It keeps doing that through the record and waits for my response. Kim Dave Peterson wrote: try ar.count wrote: Thanks Tom, You want a job :). I'm only half joking if you do contracting work let me know. I haven't written code for 20 years but I'm the closest thing my boss has. Poor thing. I tried to run this and got an error on Sheet1 so I renaned the worksheet sheet1 and it got past that. Then it stopped on worksheet2 so I created that and it got past that. Now I am getting a Run time error '438' Object doesn't support thie property or method and it is pointing to this line of code: ElseIf ar.cnt = 3 Then Tom Ogilvy wrote: Sub ProcData() dim rw as Long, rng as Range Dim sh2 as Worksheet, ar as Range rw = 1 With worksheets("Sheet1") set rng = .columns(1).Specialcells(xlblanks) End with set sh2 = Worksheets("Sheet2") for each ar in rng.Areas if ar.count = 4 then for i = 1 to 4 sh2.cells(rw,i).Value = ar(i) next elseif ar.cnt = 3 then for i = 1 to 3 sh2.cells(rw,i+1).value = ar(i) next rw = rw + 1 else msgbox ar(1).Row & " has a cnt of " & ar.rows.count end if Next ar End sub -- Regards, Tom Ogilvy " wrote: Hey fellow WVer! What part are you from? I do have a blank. This is what I have right now: YOUNG AMELIA HC 33 BOX 2093 DORCAS WV 26847 YOUNG KENNETH K & LILA D RONALD YOUNG 19 MEADOW RIDGE PETERSBURG WV 26847 YOUNG RONALD LEE & SANDRA SUE 19 MEADOW RIDGE PETERSBURG WV 26847 YOWLER ROBERT C & KAREN A HC 75 BOX 109 NEW CREEK WV 26743 ZECK ELI & JOHN EDWARD ANNABELLE ZECK 1212 FLEMING AVE FAIRMONT WV 26554 ZELLMAN WILLIAM H JR & RUTH A 2909 PAPERMILL RD PHOENIX MD 21131 ZETAH VIRGINIA BERGESTON PO BOX 296 MAYSVILLE WV 26833 a7n9 wrote: Do you have a blank row between each record? If that is the case, it would be easy to put each row of one record in to different columns. BTW. I'm from WV too. -- a7n9 ------------------------------------------------------------------------ a7n9's Profile: http://www.excelforum.com/member.php...o&userid=32149 View this thread: http://www.excelforum.com/showthread...hreadid=562556 -- Dave Peterson |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Creating a multi dimensional array using transpose - Is this t
Tom knew what you wanted to do when your groups had 3 or 4 rows, but he didn't
know what to do when a group had 1-2 or more than 4 rows. So he shows you the offending row followed by how many rows in that group. So row 4 only has a single row as well as row 8. wrote: Hey Dave, That did fix my runtime error. Now I get a message that reads "4 has a count of 1" I click OK and then it says "8 has a count of 1" It keeps doing that through the record and waits for my response. Kim Dave Peterson wrote: try ar.count wrote: Thanks Tom, You want a job :). I'm only half joking if you do contracting work let me know. I haven't written code for 20 years but I'm the closest thing my boss has. Poor thing. I tried to run this and got an error on Sheet1 so I renaned the worksheet sheet1 and it got past that. Then it stopped on worksheet2 so I created that and it got past that. Now I am getting a Run time error '438' Object doesn't support thie property or method and it is pointing to this line of code: ElseIf ar.cnt = 3 Then Tom Ogilvy wrote: Sub ProcData() dim rw as Long, rng as Range Dim sh2 as Worksheet, ar as Range rw = 1 With worksheets("Sheet1") set rng = .columns(1).Specialcells(xlblanks) End with set sh2 = Worksheets("Sheet2") for each ar in rng.Areas if ar.count = 4 then for i = 1 to 4 sh2.cells(rw,i).Value = ar(i) next elseif ar.cnt = 3 then for i = 1 to 3 sh2.cells(rw,i+1).value = ar(i) next rw = rw + 1 else msgbox ar(1).Row & " has a cnt of " & ar.rows.count end if Next ar End sub -- Regards, Tom Ogilvy " wrote: Hey fellow WVer! What part are you from? I do have a blank. This is what I have right now: YOUNG AMELIA HC 33 BOX 2093 DORCAS WV 26847 YOUNG KENNETH K & LILA D RONALD YOUNG 19 MEADOW RIDGE PETERSBURG WV 26847 YOUNG RONALD LEE & SANDRA SUE 19 MEADOW RIDGE PETERSBURG WV 26847 YOWLER ROBERT C & KAREN A HC 75 BOX 109 NEW CREEK WV 26743 ZECK ELI & JOHN EDWARD ANNABELLE ZECK 1212 FLEMING AVE FAIRMONT WV 26554 ZELLMAN WILLIAM H JR & RUTH A 2909 PAPERMILL RD PHOENIX MD 21131 ZETAH VIRGINIA BERGESTON PO BOX 296 MAYSVILLE WV 26833 a7n9 wrote: Do you have a blank row between each record? If that is the case, it would be easy to put each row of one record in to different columns. BTW. I'm from WV too. -- a7n9 ------------------------------------------------------------------------ a7n9's Profile: http://www.excelforum.com/member.php...o&userid=32149 View this thread: http://www.excelforum.com/showthread...hreadid=562556 -- Dave Peterson -- Dave Peterson |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Creating a multi dimensional array using transpose - Is this t
Dave,
The row that it is pointing out is the blank lines between the records. I am going through the code so I can understand it better. Can you let me know if I am understanding this correctly: Sub ProcData() Dim rw As Long, rng As Range Dim sh2 As Worksheet, ar As Range With Worksheets("Sheet1") Set rng = .Columns(1).SpecialCells(xlBlanks) Dim has set up storage space and allocated variables. It has set the variable rng as the Range. The code then says in worksheet 1, assign the blank lines in column 1 as the contents of that variable. Is that correct? I'm hoping to understand it so I can work with it accomplish my assignment. Thanks!!!!!!! Dave Peterson wrote: Tom knew what you wanted to do when your groups had 3 or 4 rows, but he didn't know what to do when a group had 1-2 or more than 4 rows. So he shows you the offending row followed by how many rows in that group. So row 4 only has a single row as well as row 8. wrote: Hey Dave, That did fix my runtime error. Now I get a message that reads "4 has a count of 1" I click OK and then it says "8 has a count of 1" It keeps doing that through the record and waits for my response. Kim Dave Peterson wrote: try ar.count wrote: Thanks Tom, You want a job :). I'm only half joking if you do contracting work let me know. I haven't written code for 20 years but I'm the closest thing my boss has. Poor thing. I tried to run this and got an error on Sheet1 so I renaned the worksheet sheet1 and it got past that. Then it stopped on worksheet2 so I created that and it got past that. Now I am getting a Run time error '438' Object doesn't support thie property or method and it is pointing to this line of code: ElseIf ar.cnt = 3 Then Tom Ogilvy wrote: Sub ProcData() dim rw as Long, rng as Range Dim sh2 as Worksheet, ar as Range rw = 1 With worksheets("Sheet1") set rng = .columns(1).Specialcells(xlblanks) End with set sh2 = Worksheets("Sheet2") for each ar in rng.Areas if ar.count = 4 then for i = 1 to 4 sh2.cells(rw,i).Value = ar(i) next elseif ar.cnt = 3 then for i = 1 to 3 sh2.cells(rw,i+1).value = ar(i) next rw = rw + 1 else msgbox ar(1).Row & " has a cnt of " & ar.rows.count end if Next ar End sub -- Regards, Tom Ogilvy " wrote: Hey fellow WVer! What part are you from? I do have a blank. This is what I have right now: YOUNG AMELIA HC 33 BOX 2093 DORCAS WV 26847 YOUNG KENNETH K & LILA D RONALD YOUNG 19 MEADOW RIDGE PETERSBURG WV 26847 YOUNG RONALD LEE & SANDRA SUE 19 MEADOW RIDGE PETERSBURG WV 26847 YOWLER ROBERT C & KAREN A HC 75 BOX 109 NEW CREEK WV 26743 ZECK ELI & JOHN EDWARD ANNABELLE ZECK 1212 FLEMING AVE FAIRMONT WV 26554 ZELLMAN WILLIAM H JR & RUTH A 2909 PAPERMILL RD PHOENIX MD 21131 ZETAH VIRGINIA BERGESTON PO BOX 296 MAYSVILLE WV 26833 a7n9 wrote: Do you have a blank row between each record? If that is the case, it would be easy to put each row of one record in to different columns. BTW. I'm from WV too. -- a7n9 ------------------------------------------------------------------------ a7n9's Profile: http://www.excelforum.com/member.php...o&userid=32149 View this thread: http://www.excelforum.com/showthread...hreadid=562556 -- Dave Peterson -- Dave Peterson |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Creating a multi dimensional array using transpose - Is this t
You're right. I think Tom had another(!) typo in his code.
This seemed to work ok. Option Explicit Sub ProcData2() Dim rw As Long, rng As Range, i As Long Dim sh2 As Worksheet, ar As Range rw = 1 With Worksheets("Sheet1") Set rng = .Columns(1).SpecialCells(xlCellTypeConstants) End With Set sh2 = Worksheets("Sheet2") For Each ar In rng.Areas For i = 1 To ar.Cells.Count sh2.Cells(rw, i).Value = ar(i) Next i rw = rw + 1 Next ar End Sub But it does expect all the data to be values--not formulas. And you don't have to change the names in excel. You could have changed the names in the code. With Worksheets("Sheet1") could be: With Worksheets("what ever your sheet name is here") And this line Set sh2 = Worksheets("Sheet2") would change to Set sh2 = Worksheets("your other sheet name here") As long as they match what you see in the worksheet tab, you'll be ok. wrote: Dave, The row that it is pointing out is the blank lines between the records. I am going through the code so I can understand it better. Can you let me know if I am understanding this correctly: Sub ProcData() Dim rw As Long, rng As Range Dim sh2 As Worksheet, ar As Range With Worksheets("Sheet1") Set rng = .Columns(1).SpecialCells(xlBlanks) Dim has set up storage space and allocated variables. It has set the variable rng as the Range. The code then says in worksheet 1, assign the blank lines in column 1 as the contents of that variable. Is that correct? I'm hoping to understand it so I can work with it accomplish my assignment. Thanks!!!!!!! Dave Peterson wrote: Tom knew what you wanted to do when your groups had 3 or 4 rows, but he didn't know what to do when a group had 1-2 or more than 4 rows. So he shows you the offending row followed by how many rows in that group. So row 4 only has a single row as well as row 8. wrote: Hey Dave, That did fix my runtime error. Now I get a message that reads "4 has a count of 1" I click OK and then it says "8 has a count of 1" It keeps doing that through the record and waits for my response. Kim Dave Peterson wrote: try ar.count wrote: Thanks Tom, You want a job :). I'm only half joking if you do contracting work let me know. I haven't written code for 20 years but I'm the closest thing my boss has. Poor thing. I tried to run this and got an error on Sheet1 so I renaned the worksheet sheet1 and it got past that. Then it stopped on worksheet2 so I created that and it got past that. Now I am getting a Run time error '438' Object doesn't support thie property or method and it is pointing to this line of code: ElseIf ar.cnt = 3 Then Tom Ogilvy wrote: Sub ProcData() dim rw as Long, rng as Range Dim sh2 as Worksheet, ar as Range rw = 1 With worksheets("Sheet1") set rng = .columns(1).Specialcells(xlblanks) End with set sh2 = Worksheets("Sheet2") for each ar in rng.Areas if ar.count = 4 then for i = 1 to 4 sh2.cells(rw,i).Value = ar(i) next elseif ar.cnt = 3 then for i = 1 to 3 sh2.cells(rw,i+1).value = ar(i) next rw = rw + 1 else msgbox ar(1).Row & " has a cnt of " & ar.rows.count end if Next ar End sub -- Regards, Tom Ogilvy " wrote: Hey fellow WVer! What part are you from? I do have a blank. This is what I have right now: YOUNG AMELIA HC 33 BOX 2093 DORCAS WV 26847 YOUNG KENNETH K & LILA D RONALD YOUNG 19 MEADOW RIDGE PETERSBURG WV 26847 YOUNG RONALD LEE & SANDRA SUE 19 MEADOW RIDGE PETERSBURG WV 26847 YOWLER ROBERT C & KAREN A HC 75 BOX 109 NEW CREEK WV 26743 ZECK ELI & JOHN EDWARD ANNABELLE ZECK 1212 FLEMING AVE FAIRMONT WV 26554 ZELLMAN WILLIAM H JR & RUTH A 2909 PAPERMILL RD PHOENIX MD 21131 ZETAH VIRGINIA BERGESTON PO BOX 296 MAYSVILLE WV 26833 a7n9 wrote: Do you have a blank row between each record? If that is the case, it would be easy to put each row of one record in to different columns. BTW. I'm from WV too. -- a7n9 ------------------------------------------------------------------------ a7n9's Profile: http://www.excelforum.com/member.php...o&userid=32149 View this thread: http://www.excelforum.com/showthread...hreadid=562556 -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Creating a multi dimensional array using transpose - Is this t
Dave,
That worked beautifully! Oh my gosh. I have been trying to get that to happen for 4 days. I cannot thank you (and Tom) enough for your help! How cool is that!!!! Kim Dave Peterson wrote: You're right. I think Tom had another(!) typo in his code. This seemed to work ok. Option Explicit Sub ProcData2() Dim rw As Long, rng As Range, i As Long Dim sh2 As Worksheet, ar As Range rw = 1 With Worksheets("Sheet1") Set rng = .Columns(1).SpecialCells(xlCellTypeConstants) End With Set sh2 = Worksheets("Sheet2") For Each ar In rng.Areas For i = 1 To ar.Cells.Count sh2.Cells(rw, i).Value = ar(i) Next i rw = rw + 1 Next ar End Sub But it does expect all the data to be values--not formulas. And you don't have to change the names in excel. You could have changed the names in the code. With Worksheets("Sheet1") could be: With Worksheets("what ever your sheet name is here") And this line Set sh2 = Worksheets("Sheet2") would change to Set sh2 = Worksheets("your other sheet name here") As long as they match what you see in the worksheet tab, you'll be ok. wrote: Dave, The row that it is pointing out is the blank lines between the records. I am going through the code so I can understand it better. Can you let me know if I am understanding this correctly: Sub ProcData() Dim rw As Long, rng As Range Dim sh2 As Worksheet, ar As Range With Worksheets("Sheet1") Set rng = .Columns(1).SpecialCells(xlBlanks) Dim has set up storage space and allocated variables. It has set the variable rng as the Range. The code then says in worksheet 1, assign the blank lines in column 1 as the contents of that variable. Is that correct? I'm hoping to understand it so I can work with it accomplish my assignment. Thanks!!!!!!! Dave Peterson wrote: Tom knew what you wanted to do when your groups had 3 or 4 rows, but he didn't know what to do when a group had 1-2 or more than 4 rows. So he shows you the offending row followed by how many rows in that group. So row 4 only has a single row as well as row 8. wrote: Hey Dave, That did fix my runtime error. Now I get a message that reads "4 has a count of 1" I click OK and then it says "8 has a count of 1" It keeps doing that through the record and waits for my response. Kim Dave Peterson wrote: try ar.count wrote: Thanks Tom, You want a job :). I'm only half joking if you do contracting work let me know. I haven't written code for 20 years but I'm the closest thing my boss has. Poor thing. I tried to run this and got an error on Sheet1 so I renaned the worksheet sheet1 and it got past that. Then it stopped on worksheet2 so I created that and it got past that. Now I am getting a Run time error '438' Object doesn't support thie property or method and it is pointing to this line of code: ElseIf ar.cnt = 3 Then Tom Ogilvy wrote: Sub ProcData() dim rw as Long, rng as Range Dim sh2 as Worksheet, ar as Range rw = 1 With worksheets("Sheet1") set rng = .columns(1).Specialcells(xlblanks) End with set sh2 = Worksheets("Sheet2") for each ar in rng.Areas if ar.count = 4 then for i = 1 to 4 sh2.cells(rw,i).Value = ar(i) next elseif ar.cnt = 3 then for i = 1 to 3 sh2.cells(rw,i+1).value = ar(i) next rw = rw + 1 else msgbox ar(1).Row & " has a cnt of " & ar.rows.count end if Next ar End sub -- Regards, Tom Ogilvy " wrote: Hey fellow WVer! What part are you from? I do have a blank. This is what I have right now: YOUNG AMELIA HC 33 BOX 2093 DORCAS WV 26847 YOUNG KENNETH K & LILA D RONALD YOUNG 19 MEADOW RIDGE PETERSBURG WV 26847 YOUNG RONALD LEE & SANDRA SUE 19 MEADOW RIDGE PETERSBURG WV 26847 YOWLER ROBERT C & KAREN A HC 75 BOX 109 NEW CREEK WV 26743 ZECK ELI & JOHN EDWARD ANNABELLE ZECK 1212 FLEMING AVE FAIRMONT WV 26554 ZELLMAN WILLIAM H JR & RUTH A 2909 PAPERMILL RD PHOENIX MD 21131 ZETAH VIRGINIA BERGESTON PO BOX 296 MAYSVILLE WV 26833 a7n9 wrote: Do you have a blank row between each record? If that is the case, it would be easy to put each row of one record in to different columns. BTW. I'm from WV too. -- a7n9 ------------------------------------------------------------------------ a7n9's Profile: http://www.excelforum.com/member.php...o&userid=32149 View this thread: http://www.excelforum.com/showthread...hreadid=562556 -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Creating a multi dimensional array using transpose - Is this t
I'm sure Tom appreciates the comments (and I do, too).
wrote: Dave, That worked beautifully! Oh my gosh. I have been trying to get that to happen for 4 days. I cannot thank you (and Tom) enough for your help! How cool is that!!!! Kim Dave Peterson wrote: You're right. I think Tom had another(!) typo in his code. This seemed to work ok. Option Explicit Sub ProcData2() Dim rw As Long, rng As Range, i As Long Dim sh2 As Worksheet, ar As Range rw = 1 With Worksheets("Sheet1") Set rng = .Columns(1).SpecialCells(xlCellTypeConstants) End With Set sh2 = Worksheets("Sheet2") For Each ar In rng.Areas For i = 1 To ar.Cells.Count sh2.Cells(rw, i).Value = ar(i) Next i rw = rw + 1 Next ar End Sub But it does expect all the data to be values--not formulas. And you don't have to change the names in excel. You could have changed the names in the code. With Worksheets("Sheet1") could be: With Worksheets("what ever your sheet name is here") And this line Set sh2 = Worksheets("Sheet2") would change to Set sh2 = Worksheets("your other sheet name here") As long as they match what you see in the worksheet tab, you'll be ok. wrote: Dave, The row that it is pointing out is the blank lines between the records. I am going through the code so I can understand it better. Can you let me know if I am understanding this correctly: Sub ProcData() Dim rw As Long, rng As Range Dim sh2 As Worksheet, ar As Range With Worksheets("Sheet1") Set rng = .Columns(1).SpecialCells(xlBlanks) Dim has set up storage space and allocated variables. It has set the variable rng as the Range. The code then says in worksheet 1, assign the blank lines in column 1 as the contents of that variable. Is that correct? I'm hoping to understand it so I can work with it accomplish my assignment. Thanks!!!!!!! Dave Peterson wrote: Tom knew what you wanted to do when your groups had 3 or 4 rows, but he didn't know what to do when a group had 1-2 or more than 4 rows. So he shows you the offending row followed by how many rows in that group. So row 4 only has a single row as well as row 8. wrote: Hey Dave, That did fix my runtime error. Now I get a message that reads "4 has a count of 1" I click OK and then it says "8 has a count of 1" It keeps doing that through the record and waits for my response. Kim Dave Peterson wrote: try ar.count wrote: Thanks Tom, You want a job :). I'm only half joking if you do contracting work let me know. I haven't written code for 20 years but I'm the closest thing my boss has. Poor thing. I tried to run this and got an error on Sheet1 so I renaned the worksheet sheet1 and it got past that. Then it stopped on worksheet2 so I created that and it got past that. Now I am getting a Run time error '438' Object doesn't support thie property or method and it is pointing to this line of code: ElseIf ar.cnt = 3 Then Tom Ogilvy wrote: Sub ProcData() dim rw as Long, rng as Range Dim sh2 as Worksheet, ar as Range rw = 1 With worksheets("Sheet1") set rng = .columns(1).Specialcells(xlblanks) End with set sh2 = Worksheets("Sheet2") for each ar in rng.Areas if ar.count = 4 then for i = 1 to 4 sh2.cells(rw,i).Value = ar(i) next elseif ar.cnt = 3 then for i = 1 to 3 sh2.cells(rw,i+1).value = ar(i) next rw = rw + 1 else msgbox ar(1).Row & " has a cnt of " & ar.rows.count end if Next ar End sub -- Regards, Tom Ogilvy " wrote: Hey fellow WVer! What part are you from? I do have a blank. This is what I have right now: YOUNG AMELIA HC 33 BOX 2093 DORCAS WV 26847 YOUNG KENNETH K & LILA D RONALD YOUNG 19 MEADOW RIDGE PETERSBURG WV 26847 YOUNG RONALD LEE & SANDRA SUE 19 MEADOW RIDGE PETERSBURG WV 26847 YOWLER ROBERT C & KAREN A HC 75 BOX 109 NEW CREEK WV 26743 ZECK ELI & JOHN EDWARD ANNABELLE ZECK 1212 FLEMING AVE FAIRMONT WV 26554 ZELLMAN WILLIAM H JR & RUTH A 2909 PAPERMILL RD PHOENIX MD 21131 ZETAH VIRGINIA BERGESTON PO BOX 296 MAYSVILLE WV 26833 a7n9 wrote: Do you have a blank row between each record? If that is the case, it would be easy to put each row of one record in to different columns. BTW. I'm from WV too. -- a7n9 ------------------------------------------------------------------------ a7n9's Profile: http://www.excelforum.com/member.php...o&userid=32149 View this thread: http://www.excelforum.com/showthread...hreadid=562556 -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Multi Dimensional Array | Excel Programming | |||
| Multi-Dimensional Array Let & Get | Excel Programming | |||
| Viewing Multi dimensional array | Excel Programming | |||
| Enumerating a multi-dimensional array | Excel Programming | |||
| Problem with Multi-Dimensional Array | Excel Programming | |||
Linear Mode
