Variable as lookup value in Match Function in macro

[tknight]

I new to programing in vba but have other programing experience. The one thing that keeps getting me is the formatting that vba uses.

Is there a way to use a variable as the lookup value within the match function in a macro? I have tracked my problem down to the match function not being able to handle a variable as the lookup value.

Application.WorksheetFunction.Match(Variable, .....

I'm not sure if there is a need for more code to answer this question and this is at one of the last steps in a code that has several inputs and calculations.

Thanks,
T

 

[Jonmo1]

I know this is just semantics, but I thought I'd put it out there anyway.
Your original 'Working' code is indexing column A, and finding the match in column K
But your new 'Non working' code is indexing column D and finding the match in column C


Anyway, just thought I'd put that out there, I'm sure it's not the issue.

I assume then the error is happening on this line
OverviewRow = Application.WorksheetFunction.Match(Sheets("Balanced Production Line").Range("M" & j), Sheets("Overview").Range("C13:C35"), 0)

What type of value is in Range("M" & j) ? Number, Text, could be either ?
And is column C of the same type?


Depending on your data, it's quite common for a Match type of function to occasionally return an #N/A error when used as a cell formula.
Simply because 'sometimes' the value really isn't there.

In VBA, using Application.WorksheetFunction.Match
This will cause the code to break into debug mode when the match isn't found
This doesn't mean it didn't work for previous iterations of the loop, it just came across 1 that it couldn't find.
And again, depending on your data, that may be expected. We need to 'Handle' that error.

I find the best method is to not use WorksheetFunction.
Instead of Application.WorksheetFunction.Match
Use just Application.Match, AND Dim your variable (OverviewRow) as Variant.
The same for the Index part.

That way, the code won't break into debug mode, the #N/A error will just get passed to your variable.