州转换器--将威斯康星州转化为WI，将wi转化为WI

Question

目前有一个问题，如果我把状态缩写输入到我的代码中，它就会产生N/A，如果缩写已经存在，但是小写是IE，而不是WI。

有任何方法可以根据下面的代码进行反向检查吗？

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

编辑-澄清

我很抱歉，在我离开那天之前的30秒里，我就把它输入出来了。因此，代码已经做的是搜索该行的全州名称科罗拉多，并在单元格中吐出一排以上的CO。然而，接收到的一些数据是不同的，在一个单元中会有科罗拉多，而在它下面的几个单元中会有CO，CO作为N/A输出，因为CO与正在搜索的数据不匹配。因此，请参阅下面所示的输入数据的类型。

输入码输出

WI N/A

威斯康星州WI

CA N/A

加州CA

爱荷华州

MN N/A

Answer 1

我希望在您最后一次编辑之后，我已经更好地理解了它，让我们尝试如下：

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

如果我在正确的轨道上，请告诉我。