VBA WorksheetFunction.Match problem

[TdotW]

Hi I have a dynamic range of cells that contain information belonging in another table. I'm trying to use the WorksheetFunction.Match to find the row position in the other table but I keep getting returned the same value.

 For i = 2 To NumOfRows
[INDENT]    Worksheets(2).Range("R" & i).Value = _ 
 Application.WorksheetFunction.Match("Q" & i, Batches)[/INDENT]
Next I

Batches is the reference table range.

 

[Norie]

Does this work?

 For i = 2 To NumOfRows
    Worksheets(2).Range("R" & i).Value = Application.Match(Range("Q" & i), Batches, 0)
Next I

 

[RoryA]

Your code is matching the lilteral text "Q2", "Q3" and so on, not the contents of those cells. I imagine you want:

Application.WorksheetFunction.Match(Range("Q" & i), Batches)

Note also that this is an approximate match, not an exact match.