Excel VBA - Application.Match method not finding match

Question

I have a small macro program that matches values between two worksheets (big data).

I'm using Application.Match method to find the match of a certain value in another worksheet.

The problem is, when I try to assign a row variable on the range, the result gets Row 1 instead of Row 7 where the exact match is.

Application.Match(data1, Worksheets("Data").Range("O" & lRowRange & ":O" & lLastRow), 0)`

lRowRange = 5

If Not IsError(Application.Match(data1, Worksheets("Data").Range("O" & lRowRange & ":O" & lLastRow), 0)) Then

     lMatchFound = Application.WorksheetFunction.Match(data1, Worksheets("Data").Range("O" & lRowRange & ":O" & lLastRow), 0)

     sDataFound = Sheets("Data").Cells(lMatchFound, 15)

End if

I'm not using LOOP statements to find the match because the worksheets contain large amount of data.

Hope you could help me out. Thanks in advance.

Can you explain what the problem is? Can you troubleshoot with a smaller data set, say just 10 row and then work out if there is a match or not? You have not posted enough information to provide a meaningful answer. Is there a match? How do we know? — teylyn
Sorry. Yeah. the program is working great, it's finding the exact match. The only difference is, I changed the default starting range to an actual variable which is lRowRange=5. It's like Range("O5:O10"). But the result = 1, which is weird and I don't know why. — bigbryan
I'll bet lRowRange is 4 when that happens. match doesn't always return the row on the worksheet; it returns the position within the lookup range. So if your lookup range doesn't start at row 1 on the worksheet, you have to adjust the returned value by the starting row of the lookup range. In your case, Match is returning 1 (the first row in the lookup range) and you were expecting 4 so the first row of the lookup range (i.e. lRowRange) must be row 4. — user4039065
btw, you can pass the return of Application.Match to a variant type var and test the variant with IsError so you don't have to Match twice. — user4039065

Jochen Jochen · Accepted Answer · 2018-10-25T06:30:37

You are looking for a match in O5:O10. If your searched text is in O5, then your match function give you a '1', because it is found in the first cell of your range.

Use

sDataFound = Sheets("Data").Cells(lMatchFound + lRowRange - 1, 15)

and you will be fine.

Excel VBA - Application.Match method not finding match

1 Answers