0

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.

K0D54
  • 27
  • 4
  • Use helper column(s) and indirect to reference these markers/tags you create. Alternative you can with indirect & cell(row) and or cell (address) to reference . – David Wooley - AST Apr 24 '22 at 18:30
  • I'd guess just as you use `alastrow ` in `Range("A" & alastrow + 2)` – Storax Apr 24 '22 at 18:31
  • Doh thought it was just excel . Yes I'd go with Storax exactly. – David Wooley - AST Apr 24 '22 at 18:35
  • So I can do something like Range("A" & alastrow + 2).FormulaArray = "=IFERROR(INDEX('Data'!$D$9:$D$1642,MATCH(0,IF(alastrow='Data'!$G$9:$G$1642,COUNTIF(A17,'Data'!$D$9:$D$1642), """"), 0)), """")" where I replaced the B18 with alastrow. Sorry, I am new to this. – K0D54 Apr 24 '22 at 18:49
  • Not like that, you have to use quotation marks to separate the string and the variable. https://stackoverflow.com/questions/34777486/vba-variable-string-concatenation – Storax Apr 24 '22 at 18:52

1 Answers1

1

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)), """")"
ricardogerbaudo
  • 414
  • 3
  • 9