I need a macro that will copy the corresponding shape outline of a state when that state's abbreviation is in "stateString". The following code works when searching for "Al" for Alabama, but it will also return true when "stateString" contains "Alaska" or any city with an "Al" in it.
How do you make InStr look for exact match whole words only?
If InStr(stateString, "Al") > 0 Then MainPage.Shapes(11).Copy
you can try
If InStr(stateString, "Alabama") > 0 Then MainPage.Shapes(11).Copy
or
If StrComp(stateString, "Alabama" , 1)=0 Then Mainpage.Shapes(11).Copy
Note: not tested
Thanks, but I specifically need it to compare the abbreviation. I have the code already written to compare the full state name. I just can't get it to work when it is comparing the abbreviation.
another way:
Select Case UCase(stateString)
Case "AK"
Mainpage.Shapes(10).Copy
Case "AL"
Mainpage.Shapes(11).Copy
Case "AR"
Mainpage.Shapes(12).Copy
...
End Select
So just to clarify, the stateString could be any combination of cities and states some with abbreviations, and some written out fully. Sometimes there are two letters next to each other in a city that are the abbreviation for a different state (like the Al in Albany), so I need it to only look for abbreviations if there are two letters by themselves.
I think, first you need to break the variable stateString into two words - the name of the city and the name of the state. And then if the second word consists of only two letters, then consider it an abbreviation and look for the name of the state
ok, thanks. I'll have to check into this. I also just realized that adding a space in front of the abbreviation will solve the problem I'm having the majority of the time:
If InStr(stateString, " Al") > 0 Then MainPage.Shapes(11).Copy