Why does this code (wsfunctn.Match) produce an error? SOLVED

[jbaich]

Hi everybody, I can not figure out why this code keeps giving me the error...
"run time error '1004' Unable to get the Match property of the WorksheetFuncion class"

I'm just trying to loop though the values in Column C in sheet "DO NOT DELETE" and return the corresponding row number of the match in column B in sheet "Equity"

The code that is producing the error is...

[ SubjectRow = Application.WorksheetFunction.Match("C" & i, Sheets("Equity").Range("B:B"), 0)]

FYI the argument "C" & I, is producing the expected value ("11-320-R058271293") and I've copied and pasted this cell into the Equity sheet so I know it's there... and formatted exactly the same in column B. It should be matching to the value of cell B13, so the variable SubjectRow should equal 13 for this first part of the loop...

I've tried defining the variable SubjectRow as String, Long, Double, Integer... none of those seemed to make a difference.

I've Tried removing worksheetfunction, no change... I'm totally stumped.

Please tell me what I've done wrong...

Thanks,
Joe

 

[eibi]

The "C" & i portion of your code is passing a Cell Reference rather than a Cell Value through to the Match function...

Try replacing with Range("C" & i).Value

Like so:

SubjectRow = Application.WorksheetFunction.Match(Range("C" & i).Value, Sheets("Equity").Range("B:B"), 0)​

I would probably define SubjectRow as Integer, but the others you suggested (long, double, etc.) should also work (I wouldn't use string, though)...

 

[jbaich]

you absolute BEAUTY!!!

Thanks so much!