VBA Index/Match to Fill Right and Down

[kparadise]

Hello,

I have a tab [consolidated] and a tab [adhoc]. I am trying to develop a dynamic formula in [consolidated].F2 to do an Index/Match to the [adhoc] tab and extract a bunch of data. This is super easy without VBA. By writing the code as I do below; this allows me to drag the formula to the end of my columns (which is not static); and then down.

=IF(INDEX('adhoc'!A:A, MATCH(consolidated!$A2, 'adhoc'!$B:$B, 0))="","",INDEX('adhoc'!A:A, MATCH(consolidated!$A2, 'adhoc'!$B:$B, 0)))

So my question is; how do I enter this formula in VBA in F2; then paste it RIGHT until the end of my data set (which can move) and then all the way down?


[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][consolidated][/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]D
[/TD]
[TD="align: center"]E
[/TD]
[TD="align: center"]F
[/TD]
[TD="align: center"]G
[/TD]
[TD="align: center"]H
[/TD]
[TD="align: center"]I
[/TD]
[/TR]
[TR]
[TD="align: center"]1
[/TD]
[TD]ID#[/TD]
[TD]Color[/TD]
[TD]Size[/TD]
[TD]Quan[/TD]
[TD]Risk[/TD]
[TD]AdhocD1[/TD]
[TD]AdhocD2[/TD]
[TD]AdhocD3[/TD]
[TD]AdhocD4[/TD]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD]aaa[/TD]
[TD]red[/TD]
[TD]3[/TD]
[TD]100[/TD]
[TD]Y[/TD]
[TD]index/match[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]3
[/TD]
[TD]abc[/TD]
[TD]red[/TD]
[TD]4[/TD]
[TD]150[/TD]
[TD]N[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]4
[/TD]
[TD]zyx[/TD]
[TD]blue[/TD]
[TD]6[/TD]
[TD]110[/TD]
[TD]Y[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]5
[/TD]
[TD]zzz[/TD]
[TD]red[/TD]
[TD]2[/TD]
[TD]100[/TD]
[TD]Y[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]6
[/TD]
[TD]zzz[/TD]
[TD]red[/TD]
[TD]3[/TD]
[TD]100[/TD]
[TD]Y[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

So, once the formula is in F2 which i think is the following....; "=IF(INDEX('adhoc'!C[-5], MATCH(consolidated!RC1, 'adhoc'!C2, 0))="""","""",INDEX('adhoc'!C[-5], MATCH(consolidated!RC1, 'adhoc'!C2, 0)))"; I want to copy it all the way to the end; and then copy it all the way down so all the cells (F2:I6) are filled.

 

[DanteAmor]

I'm not sure if your formula should be 'adhoc'!A:A or 'adhoc'!$A:$A

Try this

Sub test()
    Dim lc As Long, lr As Long
    Sheets("consolidated").Select
    lc = Cells(1, Columns.Count).End(xlToLeft).Column
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    With Range("F2", Cells(lr, lc))
        .FormulaR1C1 = _
        "=IF(INDEX(adhoc!C[-5], MATCH(RC1, adhoc!C2, 0))="""","""",INDEX(adhoc!C[-5], MATCH(RC1, adhoc!C2, 0)))"
    End With
End Sub

 

[kparadise]

Works great! Thank you.

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.