Non static array formula

[Tjeerd]

Hi Everyone.

I am writing a code to update a workbook. Part of this is to drag down formulas to the extend it reaches the bottom of a refreshed pivot table (used column E). I have used:

With Sheets("Circuits")
lastRow = .Cells(.Rows.Count, "E").End(xlUp).Row

.Range("h11:h" & lastRow).FormulaArray = _
"=IF(AND($F11=""0"",$D11=""""),"""",IFERROR(INDEX(DATA!AF:AF,MATCH(1,(DATA!G:G=$F11)*(DATA!Q:Q=$D11),0),1),""""))"
End With

Now I know that using this setup I will get a static drill down of my formula, I am hoping someone can teach me how to change this in to non-static

Thanks in advance!

 

[Domenic]

You'll need to first enter the formula in Range("H11"). Then you'll need to copy and paste the formulas to Range("H12:H" & lastRow)...

Range("H11").FormulaArray = " . . . "

Range("H11").Copy
Range("H12:H" & lastrow).PasteSpecial Paste:=xlPasteFormulas

 

[Tjeerd]

Hi Dominic!

Thanks a lot for your help it worked!
Only thing I changed was "Range" into ".Range"



Best regards,

Tjeerd