VBA - Find ColumnLetter - Type Mismatch

The goal is to find a certain string in an entire row (this is always Row 1). Identify the cell containing the string. Find the Column Letter of this cell (instead of the column number).

I have the function that takes Column letter from Range which I 'stole' from another page on Stackoverflow:

Function FnColFromRange(rInput As Range) As String
    FnColFromRange = Split(rInput.Address, "$")(1)
End Function

And then I have a sub to store the Column letter into a string called "colLetter"

Dim colLetter As String
With Workbooks("Wb.xlsx").Worksheets("Ws")
    Set myStringCell = .Rows(1).Find(myString)
    colLetter = FnColFromRange(myStringCell.Address)
End With

But it is always stuck on the "colLetter" line, saying Type Mismatch

1 answer

  • answered 2018-10-16 20:53 Vityata

    Check whether there is a real range returned from the .Find() method. Then, as @BruceWayne commented, probably the error is in the myStringCell.Address:

    Dim colLetter As String
    With Workbooks("Wb.xlsx").Worksheets("Ws")
        If Not .Rows(1).Find(myString) Is Nothing Then
            Set myStringCell = .Rows(1).Find(myString)
            colLetter = FnColFromRange(myStringCell)
        Else
            MsgBox "BOO!"
        End If
    End With
    

    However, something like this makes the function needless, as the myStringCell has a property named .Column and you may refer to it or use a 1 liner instead of the function:

    Dim colLetter As String
    With Workbooks("Wb.xlsx").Worksheets("Ws")
        If Not .Rows(1).Find(myString) Is Nothing Then
            Set myStringCell = .Rows(1).Find(myString)
            colLetter = Split(myStringCell.Address, "$")(1)
            Debug.Print myStringCell.Column 'if you can use the numeric column
        Else
            MsgBox "BOO!"
        End If
    End With