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

Few things here.

"SIGE" <[email protected]> wrote in message
news:[email protected]...
> 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

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" <[email protected]> wrote in message
news:[email protected]...
> 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

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 ***

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" <[email protected]> wrote in message
news:%[email protected]...
> 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 ***