Type mismatch error

[Av8tordude]

the code in wbk-a is to open wbk-b, convert abbreviated states in column A to full state names. but I'm not sure why I am getting a Type mismatch error on the code in bold

Private Sub CommandButton1_Click()
Workbooks.Open (txtFile)
Abbreviation2States
End Sub

Private Sub CommandButton2_Click()
txtFile = Application.GetOpenFilename
End Sub

Sub Abbreviation2States()
Dim c As Range
[COLOR=Red][B]For Each c In Workbooks(txtFile).Worksheets("Sheet1").Range("A:A")[/B][/COLOR]
    Select Case UCase(c)
        Case "AL"
            c = "Alabama"
        Case "AZ"
            c = "Arizona"
        Case "AR"
            c = "Arkansas"
        Case "CA"
            c = "California"
        Case "CO"
            c = "Colorado"
        Case "CT"
            c = "Connecticut"
        Case "DE"
            c = "Delaware"
        Case "DC"
            c = "DISTRICT OF COLUMBIA"
        Case "FL"
            c = "Florida"
        Case "GA"
            c = "Georgia"
        Case "ID"
            c = "Idaho"
        Case "IL"
            c = "Illinois"
        Case "IN"
            c = "Indiana"
        Case "IA"
            c = "Iowa"
        Case "KS"
            c = "Kansas"
        Case "KY"
            c = "Kentucky"
        Case "LA"
            c = "Louisiana"
        Case "ME"
            c = "Maine"
        Case "MD"
            c = "Maryland"
        Case "MA"
            c = "Massachusetts"
        Case "MI"
            c = "Michigan"
        Case "MN"
            c = "Minnesota"
        Case "MS"
            c = "Mississippi"
        Case "MO"
            c = "Missouri"
        Case "MT"
            c = "Montana"
        Case "NE"
            c = "Nebraska"
        Case "NV"
            c = "Nevada"
        Case "NH"
            c = "New Hampshire"
        Case "NJ"
            c = "New Jersey"
        Case "NM"
            c = "New Mexico"
        Case "NY"
            c = "New York"
        Case "NC"
            c = "North Carolina"
        Case "ND"
            c = "North Dakota"
        Case "OH"
            c = "Ohio"
        Case "OK"
            c = "Oklahoma"
        Case "OR"
            c = "Oregon"
        Case "PA"
            c = "Pennsylvania"
        Case "RI"
            c = "Rhode Island"
        Case "SC"
            c = "South Carolina"
        Case "SD"
            c = "South Dakota"
        Case "TN"
            c = "Tennessee"
        Case "TX"
            c = "Texas"
        Case "UT"
            c = "Utah"
        Case "VT"
            c = "Vermont"
        Case "VA"
            c = "Virginia"
        Case "WA"
            c = "Washington"
        Case "WV"
            c = "West Virginia"
        Case "WI"
            c = "Wisconsin"
        Case "WY"
            c = "Wyoming"
    End Select
Next c
End Sub

 

[mikerickson]

c is a range variable which is a mismatch with UCase. Try

Select Case UCase(CStr(c.Value))

The CStr is there to protect against another mismatch error if c contains an error value like #DIV/0.

 

[Av8tordude]

Mike, unfortunately, I'm still getting the same error. When I run this code in the same workbook, I don't receive this error

 

[mikerickson]

That is a huge loop through all of column A. You can reduce the range with

With Workbooks(txtFile).Worksheets("Sheet1").Range("A:A")
    For Each c in Range(.Cells(1,1), .Cells(.Rows.Count,1).End(xlup))
        Rem code
    Next c
End With

 

[Av8tordude]

same error continues to occur

 

[mikerickson]

You should explicitly open txtFile before looping through it.