marion12
04-28-2012, 09:14 AM
Hi everybody,
I'm working on a macro that is suppose to split the address cell, create three separate columns for street/city/zip. I've got most of the code working except for splitting the city by using InStr.
Option Explicit
Public Sub SplitAddress()
'declare variables and assign address to Worksheet object variable
Dim intLoc1 As Integer
Dim intLoc2 As Integer
Dim shtConsul As Worksheet
Dim rngCell As Range
Dim intLoc3 As Integer
Set shtConsul = _
Application.Workbooks("t10-ex-e2dc.xls").Worksheets("consultants")
'enter headings Street, City and Zip
shtConsul.Columns("c").Insert
shtConsul.Columns("d").Insert
shtConsul.Range("b1").Value = "Street"
shtConsul.Range("c1").Value = "City"
shtConsul.Range("d1").Value = "Zip Code"
shtConsul.Range("b1:d1").Font.Bold = True
'separate each full address into street, city, and zip
Set rngCell = shtConsul.Range("b2")
Do Until rngCell.Value = ""
'find location of first comma
intLoc1 = InStr(1, rngCell.Value, ",")
'find location of second comma
intLoc2 = InStr(intLoc1 + 1, rngCell.Value, ",")
'assign zip code to appropriate cell in column D
rngCell.Offset(columnoffset:=2).Value = _
Mid(String:=rngCell.Value, Start:=intLoc2 + 1, Length:=intLoc2 - 1)
'assign city to appropriate cell in column C
rngCell.Offset(columnoffset:=1).Value = _
Mid(String:=rngCell.Value, Start:=intLoc1 + 1, Length:=intLoc2 - 1)
'assign street to current cell
rngCell.Value = Left(String:=rngCell.Value, Length:=intLoc1 - 1)
'assign the address of the cell in the next row to the rngCell variable
Set rngCell = rngCell.Offset(rowoffset:=1)
Loop
'adjust the width of columns A and B
shtConsul.Columns("b:d").AutoFit
End Sub
Here's where i'm having problems:
The example of a string i'm seperating:
"111 Main St., Adolphus, 42120"
The intLoc1 = InStr(1, rngCell.Value, ",") -returns "111 Main St.," which is fine, but
the intLoc2 = InStr(intLoc1 + 1, rngCell.Value, ",") - returns "Adolphus, 42120" instead of only "Adolphus,"
Somehow the intLoc2 = InStr(intLoc1 + 1, rngCell.Value, ",") doesn't separate the city from the zipcode, returning both of them.
I think i tried everything and i run out of ideas. Any help/hints will be appreciated.
I'm working on a macro that is suppose to split the address cell, create three separate columns for street/city/zip. I've got most of the code working except for splitting the city by using InStr.
Option Explicit
Public Sub SplitAddress()
'declare variables and assign address to Worksheet object variable
Dim intLoc1 As Integer
Dim intLoc2 As Integer
Dim shtConsul As Worksheet
Dim rngCell As Range
Dim intLoc3 As Integer
Set shtConsul = _
Application.Workbooks("t10-ex-e2dc.xls").Worksheets("consultants")
'enter headings Street, City and Zip
shtConsul.Columns("c").Insert
shtConsul.Columns("d").Insert
shtConsul.Range("b1").Value = "Street"
shtConsul.Range("c1").Value = "City"
shtConsul.Range("d1").Value = "Zip Code"
shtConsul.Range("b1:d1").Font.Bold = True
'separate each full address into street, city, and zip
Set rngCell = shtConsul.Range("b2")
Do Until rngCell.Value = ""
'find location of first comma
intLoc1 = InStr(1, rngCell.Value, ",")
'find location of second comma
intLoc2 = InStr(intLoc1 + 1, rngCell.Value, ",")
'assign zip code to appropriate cell in column D
rngCell.Offset(columnoffset:=2).Value = _
Mid(String:=rngCell.Value, Start:=intLoc2 + 1, Length:=intLoc2 - 1)
'assign city to appropriate cell in column C
rngCell.Offset(columnoffset:=1).Value = _
Mid(String:=rngCell.Value, Start:=intLoc1 + 1, Length:=intLoc2 - 1)
'assign street to current cell
rngCell.Value = Left(String:=rngCell.Value, Length:=intLoc1 - 1)
'assign the address of the cell in the next row to the rngCell variable
Set rngCell = rngCell.Offset(rowoffset:=1)
Loop
'adjust the width of columns A and B
shtConsul.Columns("b:d").AutoFit
End Sub
Here's where i'm having problems:
The example of a string i'm seperating:
"111 Main St., Adolphus, 42120"
The intLoc1 = InStr(1, rngCell.Value, ",") -returns "111 Main St.," which is fine, but
the intLoc2 = InStr(intLoc1 + 1, rngCell.Value, ",") - returns "Adolphus, 42120" instead of only "Adolphus,"
Somehow the intLoc2 = InStr(intLoc1 + 1, rngCell.Value, ",") doesn't separate the city from the zipcode, returning both of them.
I think i tried everything and i run out of ideas. Any help/hints will be appreciated.