CODEWITHSUNDEEP
excelvba
K0D54
K0D54

Reputation: 27

Dynamic reference cell in Excel VBA

Is there a way to make this formula dynamic in VBA?

Range("A" & alastrow + 2).FormulaArray = "=IFERROR(INDEX('Data'!$D$9:$D$1642,MATCH(0,IF(B18='Data'!$G$9:$G$1642,COUNTIF(A17,'Data'!$D$9:$D$1642), """"), 0)), """")"

I would like B18 and A17 to change depending on where my data starts on the sheet, but I am unsure how to do that. I am not sure if it matters, but the range referenced before the start of the array is A18. Does anyone know how I could accomplish this? Thank you.

Upvotes: 0

Views: 306

Answers (1)

ricardogerbaudo
ricardogerbaudo

Reputation: 432

As some colleagues tried to explain in the comments, you have to use the & operator to concatenate (join) the static parts of your formula with the variable alastrow. Take a look:

Range("A" & alastrow + 2).FormulaArray = "=IFERROR(INDEX('Data'!$D$9:$D$1642,MATCH(0,IF(" & alastrow & "='Data'!$G$9:$G$1642,COUNTIF(" & alastrow & ",'Data'!$D$9:$D$1642), """"), 0)), """")"

Upvotes: 1

Related Questions