Solved: I need help with InStr and searching a string for characters

[marion12]

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.

[vba]

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


[/vba]
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.

[Tinbendr]

[vba] 'assign zip code to appropriate cell in column D

rngCell.Offset(columnoffset:=2).Value = _
Mid(String:=rngCell.Value, Start:=intLoc2 + 2, Length:=Len(rngCell) - intLoc2)

'assign city to appropriate cell in column C

rngCell.Offset(columnoffset:=1).Value = _
Mid(String:=rngCell.Value, Start:=intLoc1 + 1, Length:=intLoc2 - intLoc1 - 1)[/vba]

[marion12]

[vba] 'assign zip code to appropriate cell in column D

rngCell.Offset(columnoffset:=2).Value = _
Mid(String:=rngCell.Value, Start:=intLoc2 + 2, Length:=Len(rngCell) - intLoc2)

'assign city to appropriate cell in column C

rngCell.Offset(columnoffset:=1).Value = _
Mid(String:=rngCell.Value, Start:=intLoc1 + 1, Length:=intLoc2 - intLoc1 - 1)[/vba]

Thank You so much for taking your time and helping me! It works like a charm now I'll probably wouldn't figure that out on my own. Thank you so much one more time!

[Teeroy]

Instr is a complex way of achieving your outcome. Split is more economical.

You could add the following to your code:

[vba]
Dim Parse as Variant

Parse = Split(rngCell.Value, ",")
[/vba] Now Parse is an array whose first member is the street, second the city and third the Zip code. Now just assign each output cell's value to Parse(x), once for each member.