Option Compare Text Macro

[sbglobal2012]

Im using the fallowing macro that works with just the street name and suffix in A1

[pre]

Option Compare Text
Public Sub MoveSuffix()
SuffixString = "ST,Street,PL,Place,Blvd,Boulavard,AVE,Avenue,CT,Court,DR,Drive,LN,Lane"

For Each StreetAddress In Columns("A:A").SpecialCells(xlCellTypeConstants, 2)
LastSpacePos = 1
Do
LastSpacePos = InStr(LastSpacePos + 1, StreetAddress, " ")
Loop Until InStr(LastSpacePos + 1, StreetAddress, " ") = 0
Suffix = Right(StreetAddress, Len(StreetAddress) - LastSpacePos)

If InStr(1, SuffixString, Suffix) > 0 Then
StreetAddress.Offset(0, 1).Value = Suffix
StreetAddress.Value = Left(StreetAddress, LastSpacePos - 1)
End If
Next StreetAddress
End Sub

[/pre]

 

[SirJB7]

Hi, sbglobal2012!

Thank you for posting the code and sharing it. But is there any issue in which we can help you? If so, please elaborate a bit more.

Regards!

 

[Hui]

Sbglobal2012


As SirJB7 said, Thanx for the code


If you want to put code snippets here, please write a descriptive text with the code telling the readers what the code does and how they can use it. As the meaning of the code isn't apparent for people unfamiliar with VBA.


If you want to write serious posts about the code and it's uses, email me or Chandoo.


ps: You'll notice that I reformatted the code. In VBA format the code. Then paste here. Put a single ` in front of and after the code to maintain formatting