Problems with Match function

  • Thread starter Thread starter Tom
  • Start date Start date
T

Tom

Hi

I have a problem with the match function. This function requires three
parameters, but the following command fails:

Set wf = Application.WorksheetFunction
varMatch = wf.Match(strValue, ws.Range("B1:B1000"), 0)


but when I remove the third parameter, the function works?!? Is this a bug?

Tom
 
No.

0 as the third argument requires an exact match - apparently you are not
making a match, so an error is returned. When you remove the zero, match
finds the closes match, so you function works but you may get unexpected
results.
 
Tom,

Thank you for your mail. I'm a little bit confused. Please find below my
example. The first match-function works, the 2nd fails, but I don't know
why... Maybe someone can help me...

Tom



Sub Test()
Dim a As Application
Dim wf As WorksheetFunction

Set a = Application
Set wf = Application.WorksheetFunction

With ActiveSheet

'this works
Debug.Print a.Match("Hello", .Range("A1:A1000"), 0)

'this fails
Debug.Print wf.Match("Hello", .Range("A1:A1000"), 0)
End With
End Sub
 
If the second fails, then so does the first - you just are not checking for
the error. the second raises a 1004 error, the first returns a worksheet
style error.

Sub Test()
Dim a As Application
Dim wf As WorksheetFunction

Set a = Application
Set wf = Application.WorksheetFunction

With ActiveSheet

'this works
Debug.Print a.Match("Hello", .Range("A1:A1000"), 0)

'this fails
Debug.Print wf.Match("Hello", .Range("A1:A1000"), 0)
End With
End Sub

What do you get from your first debug.print

Error 2042

I would suspect.

? application.Match("XXX",Range("A1:A10"),0)
Error 2042

? cverr(xlErrNA)
Error 2042

? iserror(application.Match("XXX",Range("A1:A10"),0))
True


--
Regards,
Tom Ogilvy
 
Thank you, now it's clear...

Tom



Tom Ogilvy said:
If the second fails, then so does the first - you just are not checking for
the error. the second raises a 1004 error, the first returns a worksheet
style error.

Sub Test()
Dim a As Application
Dim wf As WorksheetFunction

Set a = Application
Set wf = Application.WorksheetFunction

With ActiveSheet

'this works
Debug.Print a.Match("Hello", .Range("A1:A1000"), 0)

'this fails
Debug.Print wf.Match("Hello", .Range("A1:A1000"), 0)
End With
End Sub

What do you get from your first debug.print

Error 2042

I would suspect.

? application.Match("XXX",Range("A1:A10"),0)
Error 2042

? cverr(xlErrNA)
Error 2042

? iserror(application.Match("XXX",Range("A1:A10"),0))
True
Click to expand...
 
Back
Top