CODEWITHSUNDEEP
excelfunctionvbamatch
user2703472
user2703472

Reputation: 45

Match function? VBA not working properly

I have a following code and i have "name" in cells B11 , B19 and B25 and i was expecting a result of 19 or 25 with the code but it returns Y=1. Could anyone tell me what is the problem?.

Sub mat()

Y = Application.Match("name", ActiveSheet.Range("B19:B30"), 0)
MsgBox Y

End Sub

Upvotes: 0

Views: 22852

Answers (3)

ayadprof
ayadprof

Reputation: 1

add slgn 

Sub mat()
    Y = Application.Match(slng(range("a4").value), ActiveSheet.Range("B19:B30"), 0)
    MsgBox Y
End Sub

ex2:

Application.WorksheetFunction.Match(CLng(TextBox1.Text), sheet110.Range("B6:B" & ls), 0)

Upvotes: 0

Mark Fitzgerald
Mark Fitzgerald

Reputation: 3068

Application.Match("name", ActiveSheet.Range("B19:B30"), 0)

The MATCH function searches for a specified item in a range of cells, and then returns the relative position of that item in the range

So parsing the parameter ActiveSheet.Range("B19:B30") means that B19 equals relative position =1.

Upvotes: 1

jpw
jpw

Reputation: 44871

According to the documentation for MATCH:

MATCH returns the position of the matched value within lookup_array, not the value itself.

and with 0as the optional third argument (match_type):

If match_type is 0, MATCH finds the first value that is exactly equal to lookup_value. Lookup_array can be in any order.

So the returned 1refers to the position on B19in the array Range("B19:B30") and the code sample is indeed behaving as expected.

Upvotes: 4

Related Questions