CODEWITHSUNDEEP
arraysexcelvba
E.Weave.
E.Weave.

Reputation: 19

Arrays in macros

How would I change the following formula to an array in a macro?

Dim lr As Long
lr = Cells(Rows.Count, 1).End(x1Up).Row
.Range (BV2:BV & lr).FormulaR1C1 = "IFERROR(INDEX(RC[12]:RC[15],MATCH(FALSE,ISBLANK(RC[12]:RC[15]),0)),"")"
End With

Upvotes: 0

Views: 37

Answers (1)

Scott Craner
Scott Craner

Reputation: 152450

  1. x1up xhould be xlup with a lowercase L not the number 1.
  2. you will need to insert the array formula using .FormulaArray in the first cell then drag down.
  3. you are missing the quotes around the range
  4. " need to be doubled in the formula string.
  5. All formula must have = to start the string
Dim lr As Long
lr = .Cells(.Rows.Count, 1).End(xlUp).Row
.Range("BV2").FormulaArray = "=IFERROR(INDEX(RC[12]:RC[15],MATCH(FALSE,ISBLANK(RC[12]:RC[15]),0)),"""")"
.Range("BV2").AutoFill .Range("BV2:Bv" & lr)

Upvotes: 3

Related Questions