Problems with Match function
 

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

Problems with Match function
 

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.

--
Regards,
Tom Ogilvy

"Tom" wrote in message
...
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

Problems with Match function
 

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




"Tom Ogilvy" schrieb im Newsbeitrag
...
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.

--
Regards,
Tom Ogilvy

"Tom" wrote in message
...
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

Problems with Match function
 

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






"Tom" wrote in message
...
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




"Tom Ogilvy" schrieb im Newsbeitrag
...
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.

--
Regards,
Tom Ogilvy

"Tom" wrote in message
...
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

Problems with Match function
 

Thank you, now it's clear...

Tom



"Tom Ogilvy" schrieb im Newsbeitrag
...
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






"Tom" wrote in message
...
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




"Tom Ogilvy" schrieb im Newsbeitrag
...
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.

--
Regards,
Tom Ogilvy

"Tom" wrote in message
...
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