CODEWITHSUNDEEP
vbaexcel-2010
bennyjim
bennyjim

Reputation: 7

VBA script to insert INDEX/MATCH formula with dynamic reference/arrays

I'm looking to simply insert an INDEX/MATCH formula in the column next to an existing selection (this will be part of a larger Sub). The reference/look-up array in the formula needs to be dynamic.

So far I've managed to successfully insert the formula. However, the reference/lookup-arrays have been manually input by me as I know the current Range (which is likely to change):

Selection.Offset(0, 1) = "=INDEX($J$3:$J$31,MATCH(INDIRECT(""RC[-2]"",0),$H$3:$H$31,0))"

How can I amend the $J$3:$J$31 and the $H$3:$H$31 to update dynamically? I've had success separately with something like 'Range("J3", Range("J3").End(xlDown))', but I can't seem to work it into the code above.

Upvotes: 0

Views: 1355

Answers (1)

SoopahTree
SoopahTree

Reputation: 116

You can integrate variables into the formula, it becomes a bit tricky, and I imagine there are better ways to accomplish this, but it's the method I use when I need dynamic ranges in formulas in VBA. I'll provide an example below that should help:

I'm assuming the J3 and H3 will remain the same, but the end of the range is what you expect to change. If this is not the case, let me know.

You're essentially replacing the "31" in your range reference with " & [variable] & " (quotes included).

EDIT: I use Sheet1 as an example for the row count; so just update that to whatever the applicable sheet & range would be.

    Dim rCount As Long
    rCount = Sheet1.Range("A" & Rows.Count).End(xlUp).Row

    Selection.Offset(0, 1) = "=INDEX($J$3:$J$" & rCount & ",MATCH(INDIRECT(""RC[-2]"",0),$H$3:$H$" & rCount & ",0))"

Upvotes: 0

Related Questions