Hello,
I found the solution below in this forum and it is very close to what I am looking for, but not quite. I have a list of contacts that has a mix of state names and states initials in the STATE column...The script below just creates a new column...I need a VBA script that will go down the list and replace the NAME of the state with the state abbreviation. Some of the state cells have bad data in them, so those cells need to be skipped...
Sub GetStateNames()
Const StateNames As String = _ "Alabama,Alaska,Arizona,Arkansas,California,Colorado,Connecticut,Delaware,Florida," & _ "Georgia,Hawaii,Idaho,Illinois,Indiana,Iowa,Kansas,Kentucky,Louisiana,Maine," & _ "Maryland,Massachusetts,Michigan,Mississippi,Missouri,Minnesota,Montana,Nebraska," & _ "Nevada,New Hampshire,New Jersey,New Mexico,New York,North Carolina,North Dakota," & _ "Ohio,Oklahoma,Oregon,Pennsylvania,Rhode Island,South Carolina,South Dakota,Tennessee," & _ "Texas,Utah,Vermont,Virginia,Washington,West Virginia,Wisconsin,Wyoming"
Const StateIds As String = _ "AL,AK,AZ,AR,CA,CO,CT,DE,FL,GA,HI,ID,IL,IN,IA,KS,KY,LA,ME,MD,MA,MI,MS,MO,MN,MT," & _ "NE,NV,NH,NJ,NM,NY,NC,ND,OH,OK,OR,PA,RI,SC,SD,TN,TX,UT,VT,VA,WA,WV,WI,WY"
Dim vecStateNames As Variant
Dim vecStateIds As Variant
Dim cell As Range
vecStateIds = Split(StateIds, ",")
vecStateNames = Split(StateNames, ",")
For Each cell In Range("A2:A200")
If cell.Value <> "" Then
cell.Offset(0, 1).Value = Application.Index(vecStateNames, Application.Match(cell.Value, vecStateIds, 0))
End If
Next cell
End Sub
From this:
AL
bad data
Alaska
CA
New Jersey
To This:
AL
bad data
AK
CA
NJ
Any Ideas?
I found the solution below in this forum and it is very close to what I am looking for, but not quite. I have a list of contacts that has a mix of state names and states initials in the STATE column...The script below just creates a new column...I need a VBA script that will go down the list and replace the NAME of the state with the state abbreviation. Some of the state cells have bad data in them, so those cells need to be skipped...
Sub GetStateNames()
Const StateNames As String = _ "Alabama,Alaska,Arizona,Arkansas,California,Colorado,Connecticut,Delaware,Florida," & _ "Georgia,Hawaii,Idaho,Illinois,Indiana,Iowa,Kansas,Kentucky,Louisiana,Maine," & _ "Maryland,Massachusetts,Michigan,Mississippi,Missouri,Minnesota,Montana,Nebraska," & _ "Nevada,New Hampshire,New Jersey,New Mexico,New York,North Carolina,North Dakota," & _ "Ohio,Oklahoma,Oregon,Pennsylvania,Rhode Island,South Carolina,South Dakota,Tennessee," & _ "Texas,Utah,Vermont,Virginia,Washington,West Virginia,Wisconsin,Wyoming"
Const StateIds As String = _ "AL,AK,AZ,AR,CA,CO,CT,DE,FL,GA,HI,ID,IL,IN,IA,KS,KY,LA,ME,MD,MA,MI,MS,MO,MN,MT," & _ "NE,NV,NH,NJ,NM,NY,NC,ND,OH,OK,OR,PA,RI,SC,SD,TN,TX,UT,VT,VA,WA,WV,WI,WY"
Dim vecStateNames As Variant
Dim vecStateIds As Variant
Dim cell As Range
vecStateIds = Split(StateIds, ",")
vecStateNames = Split(StateNames, ",")
For Each cell In Range("A2:A200")
If cell.Value <> "" Then
cell.Offset(0, 1).Value = Application.Index(vecStateNames, Application.Match(cell.Value, vecStateIds, 0))
End If
Next cell
End Sub
From this:
AL
bad data
Alaska
CA
New Jersey
To This:
AL
bad data
AK
CA
NJ
Any Ideas?