Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 *** |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 *** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
application.match | Excel Discussion (Misc queries) | |||
Application.Match | Excel Programming | |||
Application.Match | Excel Programming | |||
Application.Match | Excel Programming | |||
Application.Match | Excel Programming |