Hello,
I have been trying to figure out how to use the Application.InputBox to let the user select the range or whole column where the targeted data is.
My main goal is to have automated the conversion from US state name to US state abbreviation based on the selected column by the user. As of right now, I have the conversion part of the code with the range selected as Range("A2:A2000") but I need to change it manually each time, and it is not a really efficient process.
In advance, thanks for any help and for your time. It would be a great learning experience. Feel free to reach out if I haven't explained myself well. Thanks!
'State Cleaning Automation -> State Name to State Abbreviation
>>> use code - tags <<<
I have been trying to figure out how to use the Application.InputBox to let the user select the range or whole column where the targeted data is.
My main goal is to have automated the conversion from US state name to US state abbreviation based on the selected column by the user. As of right now, I have the conversion part of the code with the range selected as Range("A2:A2000") but I need to change it manually each time, and it is not a really efficient process.
In advance, thanks for any help and for your time. It would be a great learning experience. Feel free to reach out if I haven't explained myself well. Thanks!
'State Cleaning Automation -> State Name to State Abbreviation
>>> use code - tags <<<
Code:
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,District of Columbia,Puerto Rico"
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,DC,PR"
Dim vecStateNames As Variant
Dim vecStateIds As Variant
Dim cell As Range
vecStateIds = Split(StateIds, ",")
vecStateNames = Split(StateNames, ",")
For i = LBound(vecStateNames) To UBound(vecStateNames)
'User Input Box Range("XX:XXXX")
Range("A2:A2000").Replace vecStateNames(i), vecStateIds(i)
Next
End Sub
Attachments
Last edited by a moderator: