Application WorksheetFunction Match method

Question

I have seen a lot of Topics to the  unable to get the match property of the Worksheetfunction class  problem  But I can t get my code fixed   Why isn t this code work   rowNum   Application WorksheetFunction Match aNumber  Sheet5 Range  B16 B615    0    But a few rows higher this code works   rowNum2   Application WorksheetFunction Match originCode  Sheet7 Range  B10 B17    0    The only difference between my two lines is that in rowNum2 I used a String for look up and in rowNum a integer  Is it possible that the look up Value needs to be a String    Update on my Problem  Select Case service Case  Low Cost  MsgBox Sheet5 Cells 16   B    Gets value 0 5 Set Rng   Sheet5 Range  B16 B615       If Not IsError Application Match  0 5   Rng  0   Then  But jumps to Else     rowNum   Application Match Weight  Rng  0   Weight   0 5     MsgBox rowNum Else     MsgBox  error  End If  Case  Standard   Case  Express   Case Else  End Select    UPDATE 2      Take care that  0 5  is a String and not 0 5 So 0 5 is not  0 5   that was my error in the code

User · Accepted Answer

You are getting this error because the value cannot be found in the range. String or integer doesn't matter. Best thing to do in my experience is to do a check first to see if the value exists.

I used CountIf below, but there is lots of different ways to check existence of a value in a range.

Public Sub test()

Dim rng As Range
Dim aNumber As Long

aNumber = 666

Set rng = Sheet5.Range("B16:B615")

    If Application.WorksheetFunction.CountIf(rng, aNumber) > 0 Then

        rowNum = Application.WorksheetFunction.Match(aNumber, rng, 0)

    Else
        MsgBox aNumber & " does not exist in range " & rng.Address
    End If

End Sub

ALTERNATIVE WAY

Public Sub test()
    Dim rng As Range
    Dim aNumber As Variant
    Dim rowNum As Long

    aNumber = "2gg"

    Set rng = Sheet5.Range("B1:B20")

    If Not IsError(Application.Match(aNumber, rng, 0)) Then
        rowNum = Application.Match(aNumber, rng, 0)
        MsgBox rowNum
    Else
        MsgBox "error"
    End If
End Sub

OR

Public Sub test()
    Dim rng As Range
    Dim aNumber As Variant
    Dim rowNum As Variant

    aNumber = "2gg"

    Set rng = Sheet5.Range("B1:B20")

    rowNum = Application.Match(aNumber, rng, 0)

    If Not IsError(rowNum) Then
        MsgBox rowNum
    Else
        MsgBox "error"
    End If
End Sub

[excel] Application.WorksheetFunction.Match method

Examples related to excel

Examples related to vba