How to use Match, when I'm looking sometimes for numbers, and sometimes for text?

Question

When I look for text, it returns OK.. But when I look for numbers, it give me an error "Run-time error '13': type missmatch". The cells are formated as "General", here is the code I have.

Private Sub CommandButton1_Click()

If Me.TextBox1.Value <> "" Then
Dim sh As Worksheet
Dim i As Long
Set sh = ThisWorkbook.Sheets("Sheet1")

strSerialNum = Me.TextBox1.Value

i = Application.Match(strSerialNum, sh.Range("A:A"), 0)

    Me.TextBox2.Value = sh.Range("B" & i).Value

End If

End Sub

This might be of interest: https://stackoverflow.com/questions/27302794/application-match-gives-type-mismatch — DecimalTurn, Aug 01 '20 at 22:53
Did you define strSerialNum As String? If yes, try to define it As Variant instead. — DecimalTurn, Aug 01 '20 at 22:57
I've tried to define strSerialNum as String and Variant, still only working with letters, defining as Double it handles numbers, but not letters, so I guess I'm stuck here, the whole picture here is I'm building a "Update userform", and typing the keyword brings information already in the sheet for me to update them, some keywords are numbers others are letters and even mixed ones. — Gabriel Lorscheiter, Aug 02 '20 at 00:40

score 2 · Accepted Answer · answered Aug 02 '20 at 00:37

You can check if the value from your textbox is numeric using the IsNumeric() function. If you use a Variant instead of a String variable, you can simply convert it to a Decimal subtype to avoid the type mismatch.

Private Sub CommandButton1_Click()
    Dim sh As Worksheet
    Dim i As Long
    Dim vSerialNum As Variant
    
    Set sh = ThisWorkbook.Sheets("Sheet1")

    If Me.TextBox1.Value <> "" Then

        vSerialNum = Me.TextBox1.Value
        
        'If the value from the input box is numeric, convert it to a decimal subtype
        If IsNumeric(vSerialNum) Then vSerialNum = (CDec(vSerialNum))
    
        i = Application.Match(vSerialNum, sh.Range("A:A"), 0)

        Me.TextBox2.Value = sh.Range("B" & i).Value

    End If

End Sub

Variatus · Answer 2 · 2020-08-02T00:59:27.770

A TextBox contains a string, not a number, although the string could be numeric. Therefore it's logical to ensure that a search for a string is conducted, not a number. Try

strSerialNum = Me.TextBox1.Text
or even
strSerialNum = Cstr(Me.TextBox1.Text)

Now the question is what you have in A:A. A General format is asking for trouble because you leave the choice of data type to Excel. If you intend to look for a string make sure that column A is formatted as Text.

The long and the short of the above is that you can't look for a Text in a column of numbers, nor can you look for a number in a column of strings. You can control which data type you look for and you can change the data type depending upon which column you search in, provided that you do know which data type you have in which column.

If you can't control the data type of your lookup columns the only alternative is to try searching one type first and then the other using On Error Resume Next and If Err Then.

How to use Match, when I'm looking sometimes for numbers, and sometimes for text?

2 Answers2