State Converter - Turning Wisconsin into WI and wi into WI

Question

currently having an issue where if I feed state abbreviations into my code it spits out N/A if the abbreviation is already there but lower case IE wi instead of WI.

Is there any way to reverse check based on the code below?

Sub Convert_States()
Dim Ws As Worksheet
Const StateNames As String = _
    "Alabama,Alaska,Alberta,Arizona,Arkansas,British Columbia,California,Colorado,Connecticut,Delaware,District of Columbia,Florida,Georgia,Hawaii,Idaho,Illinois,Indiana,Iowa,Kansas,Kentucky,Louisiana,Maine,Manitoba,Maryland,Massachusetts,Michigan,Minnesota,Mississippi,Missouri,Montana,Nebraska,Nevada,New Brunswick,New Hampshire,New Jersey,New Mexico,New York,Newfoundland,North Carolina,North Dakota,Nova Scotia,Ohio,Oklahoma,Ontario,Oregon,Pennsylvania,Prince Edward Island,Quebec,Rhode Island,saskatchewan,South Carolina,South Dakota,Tennessee,Texas,Utah,Vermont,Virginia,Washington,West Virginia,Wisconsin,Wyoming"
Const StateIds As String = _
    "AL,AK,AB,AZ,AR,BC,CA,CO,CT,DE,DC,FL,GA,HI,ID,IL,IN,IA,KS,KY,LA,ME,MB,MD,MA,MI,MN,MS,MO,MT,NE,NV,NB,NH,NJ,NM,NY,NF,NC,ND,NS,OH,OK,ON,OR,PA,PE,PQ,RI,SK,SC,SD,TN,TX,UT,VT,VA,WA,WV,WI,WY"
Dim StNames As Variant
Dim StIds As Variant
Dim c As Range

''''''LR Code is extraneous - Disabled for time being.
'Dim LR As Long
'LR = ActiveSheet.UsedRange.Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
''''''

'dialogue box
    Dim rng As Range
    Set rng = Application.InputBox("Select State column", "Obtain Range Object", Type:=8)
'insert column to right
Columns(rng.Address).Offset(0, 1).Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

Application.ScreenUpdating = False
StIds = Split(StateIds, ",")
StNames = Split(StateNames, ",")

For Each c In Range(rng.Address)
    If c.Value <> "" Then
        c.Offset(0, 1).Value = Application.Index(StIds, Application.Match(c.Value, StNames, 0))
    End If
Next c

Columns.AutoFit
Range("B1").Select
Application.ScreenUpdating = True
End Sub

Edit - Clarification

My apologies was typing that out in the last 30 seconds before I left for the day. So what the code does already is search the row for full state name Colorado and spits out CO in the cell one row over. However some of the data received varies and will have Colorado in one cell, and a few cells down it will have CO, the CO gets output as a N/A as CO does not match up against what is being searched. So see below for kind of idea of what the incoming data appears as.

Input Code Outputs
WI N/A
Wisconsin WI
CA N/A
California CA
Iowa IA
MN N/A

[Convert the string to uppercase first](https://stackoverflow.com/questions/22258729/function-to-convert-string-to-upper-case)? Side note: you usually want to [avoid using select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) in your code — cybernetic.nomad, Mar 10 '20 at 21:31
By "if the abbreviation is already there" you mean a case were you have "Alabama" as c.Value and "al" as c.Offset(0,1).Value before you run the macro? — q0mlm, Mar 10 '20 at 22:43
That post on using dim is great! I will keep that book marked for quite awhile. — JudgeCx, Mar 11 '20 at 14:58
@mlm0b11011 Hey, so basically is c.Value is WI it results in a #/NA instead of just WI, whereas if its Wisconsin it gives the proper abbreviation of WI. See edit in original question for more info. Thank you!! — JudgeCx, Mar 11 '20 at 14:59

q0mlm · Accepted Answer · 2020-03-11T17:29:37.023

1

I hope I have understood it better following your last edit, let's try this:

For Each c In Range(Rng.Address)
    If c.Value <> "" And Len(Trim(c.Value)) = 2 Then
        c.Offset(0, 1).Value = Application.Index(StIds, Application.Match(UCase(c.Value), StIds, 0))
    ElseIf c.Value <> "" And Len(Trim(c.Value)) <> 2 Then
          c.Offset(0, 1).Value = Application.Index(StIds, Application.Match(Trim(StrConv(c.Value, vbProperCase)), StNames, 0))
    End If
Next c

Let me know if I am on the right track.

edited Mar 11 '20 at 17:29

answered Mar 10 '20 at 22:23

q0mlm

313
1
3
10

Gave a quick update to my original post, didnt realize how badly it was worded until just now. – JudgeCx Mar 11 '20 at 14:55
@JudgeCx I have udpated the code following your clarifications, try it out and let me know ;) – q0mlm Mar 11 '20 at 17:34
This worked perfectly! Thank you so much I have spent a majority of the morning trying to get this to work properly! @mlm0b11011 – JudgeCx Mar 11 '20 at 17:38

State Converter - Turning Wisconsin into WI and wi into WI

1 Answers1