Application.Match
 

Hi there

Why does Application.Match or Application.Worksheetfunction.Match
not work with integers as look-up value?

I read there is a difference between both functions ...but coul'n't
get in what way there is a difference...

'fill row1 wihth the numbers 1,2,3,4,5,... and I get a type mismatch,
error 13 on running underneath sub.

Sub test()
Dim sige As Integer
sige = Application.WorksheetFunction.Match("1", "Sheet1!$A$1:$K$1", 0)
MsgBox sige
End Sub

Application.Match
 

Few things here.

"SIGE" wrote in message
om...
Why does Application.Match or Application.Worksheetfunction.Match
not work with integers as look-up value?

Match does work with an integer value, but you have to get the syntax
correct.

When comparing with integers, why do you use a string?
The range is not the same in VBA as in Excel, you have to adapt.

I read there is a difference between both functions ...but coul'n't
get in what way there is a difference...

The difference lies in its error handling. In XL97, there was a problem with
the WorksheetFunction form of Match that did not manifest with
Application.Match. Myrna Larson reporst that she has experienced that
problem in later versions. For that reason, it is generally agrreed to be
safe rather than sorry and use Application.Match.

'fill row1 wihth the numbers 1,2,3,4,5,... and I get a type mismatch,
error 13 on running underneath sub.

Sub test()
Dim sige As Integer
sige = Application.WorksheetFunction.Match("1", "Sheet1!$A$1:$K$1", 0)
MsgBox sige
End Sub

With correct syntax, and some error handling, it should work okay

Sub test()
Dim sige
On Error Resume Next
sige = Application.Match(99, Range("Sheet3!$A$1:$K$1"), 0)
If Not IsError(sige) Then
MsgBox sige
End If
On Error GoTo 0
End Sub

Application.Match
 

Your problem is that you are looking up the string "1". "1" does not equal
1 which is what is containted in your cells.

Sub test()
Dim sige As Integer
sige = Application.WorksheetFunction.Match(1, "Sheet1!$A$1:$K$1", 0)
MsgBox sige
End Sub

or

Sub test()
Dim sige As Integer
sige = Application.Match(1, "Sheet1!$A$1:$K$1", 0)
MsgBox sige
End Sub

Either should work although in my opinion, Application.Match should be the
most reliable.

--
Regards,
Tom Ogilvy


"SIGE" wrote in message
om...
Hi there

Why does Application.Match or Application.Worksheetfunction.Match
not work with integers as look-up value?

I read there is a difference between both functions ...but coul'n't
get in what way there is a difference...

'fill row1 wihth the numbers 1,2,3,4,5,... and I get a type mismatch,
error 13 on running underneath sub.

Sub test()
Dim sige As Integer
sige = Application.WorksheetFunction.Match("1", "Sheet1!$A$1:$K$1", 0)
MsgBox sige
End Sub

Application.Match
 

Hi Tom,

I tried to get your solution to work ... I've too much respect to say
your solution is not correct. But working with XL97 SR2 you definitely
need to put

sige = Application.Match(1, Range("Sheet1!$A$1:$K$1"), 0)
instead of:
sige = Application.Match(1, "Sheet1!$A$1:$K$1", 0)

But it is working now!
Thanks a lot Bob and Tom!!!

Sige


"NOSPAM" to be removed for direct mailing...

*** Sent via Developersdex http://www.developersdex.com ***

Application.Match
 

To tell the truth, I focused on what you asked - I wasn't looking for syntax
errors, so I missed the fact you had a bad range reference in your code and
I used you code as an example.

You are correct that you need to use the proper syntax to define your range.
However, I can't get the error you described until I fix the syntax error
and then use Application.Match. In that case, my suggestion allowed it to
work properly.

--
Regards,
Tom Ogilvy





"SIGE" wrote in message
...
Hi Tom,

I tried to get your solution to work ... I've too much respect to say
your solution is not correct. But working with XL97 SR2 you definitely
need to put

sige = Application.Match(1, Range("Sheet1!$A$1:$K$1"), 0)
instead of:
sige = Application.Match(1, "Sheet1!$A$1:$K$1", 0)

But it is working now!
Thanks a lot Bob and Tom!!!

Sige


"NOSPAM" to be removed for direct mailing...

*** Sent via Developersdex http://www.developersdex.com ***