Index Match to Fill Columns in VBA

[Clarkwr3]

I cannot for the life of me figure out how to call out a named range when using Index match in VBA. From what I can find it sounds like maybe you cant but then also I cant find a workaround.

What Im looking for is to replace "Range("D2")" in the match function with a named range. Not in VBA I would enter the formula and fill down but that just copies the same value when you use autofill.

Heres what I have. DR and OP are named worksheets. The Part.Value returns the same part down the entire range based on the first match (expected). Now how to I tell it to look at cell B3 when filling A3? I had a range named "WO" that has all of the values in column D but cannot put "WO" into the match.

Dim DataTbl As Range
Dim MatchRng As Range
DR.Activate
Set DataTbl = DR.Range("$A:$R")
Set MatchRng = DR.Range("$C:$C")
'
Dim Parts As Range
OP.Activate
Set Parts = OP.Range(Cells(2, 2), Cells(L, 2))
'
Parts.Value = Application.WorksheetFunction.Index(DataTbl, Application.WorksheetFunction.Match(Range("D2"), MatchRng, 0), 1)

I appreciate the help in advance, new to VBA so this has been an all day event

 

[mart37]

For Each Cell In Parts
Cell.Value = Application.WorksheetFunction.Index(DataTbl, Application.WorksheetFunction.Match(Cell.Offset(, 1), MatchRng, 0), 1)
Next

 

[Clarkwr3]

Dude you are awesome thank you! I was reading up on arrays last night until my head was spinning. Half the battle is knowing what keywords to google so Offset will be a useful addition.