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?

[consolidated]	A	B	C	D	E	F	G	H	I
1	ID#	Color	Size	Quan	Risk	AdhocD1	AdhocD2	AdhocD3	AdhocD4
2	aaa	red	3	100	Y	index/match
3	abc	red	4	150	N
4	zyx	blue	6	110	Y
5	zzz	red	2	100	Y
6	zzz	red	3	100	Y

<tbody>
</tbody>

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.