Application.WorksheetFunction.Match method

by

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

More Related Contents:

  1. we want to search for an empty column and add items to that column using a vba form
  2. VBA code – Sumbycellscolor
  3. Error in finding last used cell in Excel with VBA
  4. How to use the Implements in Excel VBA
  5. How to pause for specific amount of time? (Excel/VBA)
  6. What is the difference between dim and set in vba
  7. Quicker way to get all unique values of a column in VBA?
  8. How to set recurring schedule for xlsm file using Windows Task Scheduler
  9. Change date format using substitute or replace
  10. VBA: Convert Text to Number
  11. Concatenate cell data into another data if values matches
  12. Iterating through populated rows
  13. Using VBA to detect which decimal sign the computer is using
  14. Referencing Excel Userform Control Name Value From String (VBA)
  15. Loop through each row of a range in Excel
  16. Fill formula down till last row in column
  17. Outputting Excel rows to a series of text files
  18. How to create a range from two ranges in VBA?
  19. XML parse VBA excel (function trip, & MSXML2.DOMDocument)
  20. If string contains word from list, return value adjacent to list value
  21. Refreshing all the pivot tables in my excel workbook with a macro
  22. excel vba freeze pane without select
  23. Converting a string representation of a constant into a constant?
  24. VBA WinHTTP to download file from password proteced https website
  25. Create folder path if does not exist (saving from VBA)
  26. Access files with long paths (over 260)
  27. Concatenate multiple ranges using vba
  28. What is the different between IsNull, IsEmpty, =Empty, and an empty string ie “” and why might I use variants
  29. How to detect if user select cancel InputBox VBA Excel
  30. Change HTML email body font type and size in VBA

Leave a Comment