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

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

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




"[email protected]" 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
>
>

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
>
>
>
>
> "[email protected]" 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
> >
> >

try ar.count

[email protected] 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
> >
> >
> >
> >
> > "[email protected]" 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

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
>
> [email protected] 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
> > >
> > >
> > >
> > >
> > > "[email protected]" 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

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.



[email protected] 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
> >
> > [email protected] 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
> > > >
> > > >
> > > >
> > > >
> > > > "[email protected]" 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,
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.
>
>
>
> [email protected] 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
> > >
> > > [email protected] 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
> > > > >
> > > > >
> > > > >
> > > > >
> > > > > "[email protected]" 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

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.

[email protected] 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.
> >
> >
> >
> > [email protected] 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
> > > >
> > > > [email protected] 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
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > > "[email protected]" 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,
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.
>
> [email protected] 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.
> > >
> > >
> > >
> > > [email protected] 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
> > > > >
> > > > > [email protected] 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
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > "[email protected]" 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,
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.
>
> [email protected] 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.
> > >
> > >
> > >
> > > [email protected] 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
> > > > >
> > > > > [email protected] 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
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > "[email protected]" 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,
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.
>
> [email protected] 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.
> > >
> > >
> > >
> > > [email protected] 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
> > > > >
> > > > > [email protected] 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
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > "[email protected]" 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

I'm sure Tom appreciates the comments (and I do, too).



[email protected] 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.
> >
> > [email protected] 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.
> > > >
> > > >
> > > >
> > > > [email protected] 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
> > > > > >
> > > > > > [email protected] 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
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > > "[email protected]" 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