Mismatch with match function

[grkchakri]

Hi All

I am trying to write a match function in command button to look up certain values
my values are in range a

Prod code
10060
10061
10062
10063
10064

on the same sheet i am trying to put a command button trying to match
user input with prod code and find the relative position of it and just msgbox the number
but it through mismatch, should this code also use index function if so how ?

Also wanted to know difference between application.match and application.worksheetfunction.match

Please Login or Register  to view this content.

[adyteo]

use WorksheetFunction.Match

[sktneer]

You are getting Type Mismatch error because you have declared x as string while values in col. A are numbers.
Declare x as long like below

Please Login or Register  to view this content.

[romperstomper]

Application.Match will return an error value that you can test with IsError if the lookup value is not found; Application.Worksheetfunction.Match will raise a runtime error if the value is not found. Other than that, they behave the same way.

If your mismatch error occurs on the Msgbox line, your lookup value wasn't found and y contains an error value.