Array Formulas in VBA

[rajanagrawal]

Hello,

I'm struggling with an array formula in VBA which is more than 255 characters. Per the posts I found on several forums, I split the formula in two parts, with first part having a place holder for the second part and then replacing the place holder with the second part of the formula. The code writes the array formula in the designated cell but does not replace the place holder with the second part of the formula and I have not able to find the mistake in the code. Please help me on this. Thank you in advance.

Here's the extract of the code -

Dim PPvtSht as String, CPvtSht as String, myFormula1 as String, myFormula2 as String
Dim DRPPLr as Long, DRCPLr as Long
Dim PvtItm as PivotItem

myFormula1 = "=IFERROR(IFERROR(INDEX(" & PPvtSht & "!R5C2:R" & DRPPLr & "C2,MATCH(1,(" & PPvtSht & "!R5C1:R" & DRPPLr & "C1=""FINANCE"")*(" & PPvtSht & "!R5C2:R" & DRPPLr & "C2=" & PvtItm.Name & "!RC[-1]&""_""&" & PvtItm.Name & "!RC[-2]),0)),99999),999999)"

myFormula2 = "INDEX(" & CPvtSht & "!R5C2:R" & DRCPLr & "C2,MATCH(1,(" & CPvtSht & "!R5C1:R" & DRCPLr & "C1=""FINANCE"")*(" & CPvtSht & "!R5C2:R" & DRCPLr & "C2=" & PvtItm.Name & "!RC[-1]&""_""&" & PvtItm.Name & "!RC[-2]),0))),"""")"

With VarSht.Range("C14")
.FormulaArray = myFormula1
.Replace "99999),999999)", myFormula2
End With

Thanks...Rajan

 

[StephenCrump]

You've written myformula2 using R1C1 notation, which Excel won't recognise as a valid formula when it's in A1, therefore the .Replace won't work.

Try with myformula2 in A1 notation.

 

[rajanagrawal]

Thanks a lot for your response.

I was finding it difficult to add the variables to formula2 in A1 notation - like the worksheet name "CPvtSht", last occupied row "DRCPLr" etc. So, instead of changing the formula to A1 notation, I added the Application.ReferenceStyle = xlR1C1 before the code starts reading the formula code and changed it back to xlA1 style after the code has written the formula and it worked.

Thanks...Rajan