software

I have a list of addresses that are all in column A that I need to transform
into rows to export into ACT.

I am a total newbie to macros and VB.

The addresses are all seperated by a cell with the number 100. An example
of the list follows. There are about 350 addresses.

100
Realtor
Sherry Abell
Realty One Medina
3565 Medina Rd
Medina OH 44256
877 7233211

8777233211
100
Realtor
Nancy Adams
Kovack Realty Inc - Wadsworth
Ste 105 1392 High Street
Wadsworth OH 44281
800 8378883

100
Realtor
Terry Albright
Smucker Realty
6605 Smucker Dr.
Westfield Center OH 44251
330 8875286
100
Realtor
Mary Anderson
Realty One Medina
3565 Medina Rd
Medina OH 44256
877 7233211

8882010910

Thanks!

It's usually pretty difficult to parse this kind of stuff into the correct
columns.

I tried to dump the numeric entries (phone number???) to column A.
The email address to column B
the address with zip code to column C

You can rearrange those columns when you're cleaning the data.

ps. If you have multiple phone numbers in any group (or multiple email accounts
in any group), then only the last will be kept.

Option Explicit
Sub testme()
Dim CurWks As Worksheet
Dim NewWks As Worksheet
Dim oRow As Long
Dim oCol As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim myVal As Variant

Set CurWks = ActiveSheet
Set NewWks = Worksheets.Add

oRow = 1
With CurWks
FirstRow = 1
LastRow = .Cells(.Rows.Count, quot;Aquot;).End(xlUp).Row

For iRow = FirstRow To LastRow
myVal = .Cells(iRow, quot;Aquot;).Value
If myVal = 100 Then
oRow = oRow 1
oCol = 4
ElseIf IsNumeric(Application.Substitute(myVal, quot; quot;, quot;quot;)) Then
'phone number
oCol = 1
ElseIf LCase(myVal) Like quot;*@*quot; Then
'email address
oCol = 2
ElseIf IsNumeric(Trim(Right(myVal, 5))) Then
'zip code??
oCol = 3
Else
oCol = oCol 1
End If
NewWks.Cells(oRow, oCol).Value = .Cells(iRow, quot;Aquot;).Value
Next iRow
End With
End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
www.mvps.org/dmcritchie/excel/getstarted.htm

Benjy S wrote:
gt;
gt; I have a list of addresses that are all in column A that I need to transform
gt; into rows to export into ACT.
gt;
gt; I am a total newbie to macros and VB.
gt;
gt; The addresses are all seperated by a cell with the number 100. An example
gt; of the list follows. There are about 350 addresses.
gt;
gt; 100
gt; Realtor
gt; Sherry Abell
gt; Realty One Medina
gt; 3565 Medina Rd
gt; Medina OH 44256
gt; 877 7233211
gt;
gt; 8777233211
gt; 100
gt; Realtor
gt; Nancy Adams
gt; Kovack Realty Inc - Wadsworth
gt; Ste 105 1392 High Street
gt; Wadsworth OH 44281
gt; 800 8378883
gt;
gt; 100
gt; Realtor
gt; Terry Albright
gt; Smucker Realty
gt; 6605 Smucker Dr.
gt; Westfield Center OH 44251
gt; 330 8875286
gt; 100
gt; Realtor
gt; Mary Anderson
gt; Realty One Medina
gt; 3565 Medina Rd
gt; Medina OH 44256
gt; 877 7233211
gt;
gt; 8882010910
gt;
gt; Thanks!

--

Dave Peterson

Thank you so much ! That was a life saver!

quot;Dave Petersonquot; wrote:

gt; It's usually pretty difficult to parse this kind of stuff into the correct
gt; columns.
gt;
gt; I tried to dump the numeric entries (phone number???) to column A.
gt; The email address to column B
gt; the address with zip code to column C
gt;
gt; You can rearrange those columns when you're cleaning the data.
gt;
gt; ps. If you have multiple phone numbers in any group (or multiple email accounts
gt; in any group), then only the last will be kept.
gt;
gt; Option Explicit
gt; Sub testme()
gt; Dim CurWks As Worksheet
gt; Dim NewWks As Worksheet
gt; Dim oRow As Long
gt; Dim oCol As Long
gt; Dim FirstRow As Long
gt; Dim LastRow As Long
gt; Dim iRow As Long
gt; Dim myVal As Variant
gt;
gt; Set CurWks = ActiveSheet
gt; Set NewWks = Worksheets.Add
gt;
gt; oRow = 1
gt; With CurWks
gt; FirstRow = 1
gt; LastRow = .Cells(.Rows.Count, quot;Aquot;).End(xlUp).Row
gt;
gt; For iRow = FirstRow To LastRow
gt; myVal = .Cells(iRow, quot;Aquot;).Value
gt; If myVal = 100 Then
gt; oRow = oRow 1
gt; oCol = 4
gt; ElseIf IsNumeric(Application.Substitute(myVal, quot; quot;, quot;quot;)) Then
gt; 'phone number
gt; oCol = 1
gt; ElseIf LCase(myVal) Like quot;*@*quot; Then
gt; 'email address
gt; oCol = 2
gt; ElseIf IsNumeric(Trim(Right(myVal, 5))) Then
gt; 'zip code??
gt; oCol = 3
gt; Else
gt; oCol = oCol 1
gt; End If
gt; NewWks.Cells(oRow, oCol).Value = .Cells(iRow, quot;Aquot;).Value
gt; Next iRow
gt; End With
gt; End Sub
gt;
gt; If you're new to macros, you may want to read David McRitchie's intro at:
gt; www.mvps.org/dmcritchie/excel/getstarted.htm
gt;
gt; Benjy S wrote:
gt; gt;
gt; gt; I have a list of addresses that are all in column A that I need to transform
gt; gt; into rows to export into ACT.
gt; gt;
gt; gt; I am a total newbie to macros and VB.
gt; gt;
gt; gt; The addresses are all seperated by a cell with the number 100. An example
gt; gt; of the list follows. There are about 350 addresses.
gt; gt;
gt; gt; 100
gt; gt; Realtor
gt; gt; Sherry Abell
gt; gt; Realty One Medina
gt; gt; 3565 Medina Rd
gt; gt; Medina OH 44256
gt; gt; 877 7233211
gt; gt;
gt; gt; 8777233211
gt; gt; 100
gt; gt; Realtor
gt; gt; Nancy Adams
gt; gt; Kovack Realty Inc - Wadsworth
gt; gt; Ste 105 1392 High Street
gt; gt; Wadsworth OH 44281
gt; gt; 800 8378883
gt; gt;
gt; gt; 100
gt; gt; Realtor
gt; gt; Terry Albright
gt; gt; Smucker Realty
gt; gt; 6605 Smucker Dr.
gt; gt; Westfield Center OH 44251
gt; gt; 330 8875286
gt; gt; 100
gt; gt; Realtor
gt; gt; Mary Anderson
gt; gt; Realty One Medina
gt; gt; 3565 Medina Rd
gt; gt; Medina OH 44256
gt; gt; 877 7233211
gt; gt;
gt; gt; 8882010910
gt; gt;
gt; gt; Thanks!
gt;
gt; --
gt;
gt; Dave Peterson
gt;