Application.Match Error

[nigelk]

Hi all, can anyone tell me why I get a "type mismatch" error when trying to run this?

RowNum = Application.Match("A. Smith", Sheets("sheet2").Range("a1:a500"), 0)

thanks,Nigel

 

[jeffreybrown]

Hi Nigel,

Not sure, but this worked for me. Did you Dim your variable correctly?

Option Explicit

Sub test()
    Dim RowNum As Long
    Dim Rng As Range
    Set Rng = Sheets("Sheet2").Range("A1:A500")
    RowNum = Application.Match("A. Smith", Rng, 0)
    MsgBox RowNum
End Sub

 

[jim may]

Looks and works OK, for me...

 

[nigelk]

Thanks Jeff, but still getting same error?

Nigel.

 

[jeffreybrown]

You probably need to place a quick test.

Create a new sheet and title it Sheet2.

In cell A7 put A. Smith

Place the following code in a standard module >> close the VBE >> press Alt + F8 >> run test

Msgbox should return 7

Sub test()
    Dim RowNum As Long
    Dim Rng As Range
    Dim strFind As String
    strFind = "A. Smith"
    Set Rng = Sheets("Sheet2").Range("A1:A500")
    RowNum = Application.Match(strFind, Rng, 0)
    MsgBox RowNum
End Sub

 

[Domenic]

Application.Match returns either a Double or error. As such, RowNum should be declared as a Variant type...

Dim RowNum as Variant

 

[jeffreybrown]

Hi Domenic,

How come it worked when as Long was used or will it possibly run into an error at some point?

 

[nigelk]

Thanks to you all. Working fine now.

Nigel.

 

[Domenic]

Hi Domenic,

How come it worked when as Long was used or will it possibly run into an error at some point?

Hi Jeff,

Long cannot accept an error. Therefore, a type mismatch error occurs whenever Application.Match returns an error. Hence, the variable is declared as a Variant data type, which can accept errors along with other types of data.

 

[jeffreybrown]

Thanks Domenic, I see now. Many thanks