Long Array Formula VBA

[WE2286]

HI ALL,

THIS IS MY FIRSTPOST SO HOPEFULLY I'M IN LINE W FORUM POLICIES.

I HAVE SET UP A MACRO TO PULL DATA THROUGH FROM A SECOND OPEN WORKBOOK TO A SHEET. IN THIS SHEET I WANT TO USE A FEW OF THE DATA POINTS TO CALCULATE A VALUE.

I HAVE THE FOLLOWING FORMULA THAT NEEDS TO BE APPLIED TO THE RANGE OF CELLS, WHICH ARE DYNAMIC BASED UPON THE ACTIVE RANGE OF THE OTHER WORKBOOK:

---

=MAX(INDIRECT($CM$1&$CM$2&ROW(CF13)),INDIRECT($CM$1&$CK$3&ROW(CF13)))+(SUMIF(INDIRECT($CM$1&$CK$1&$CG$1&":"&$CK$1&$CG$2),INDIRECT($CM$1&$CK$1&ROW(CF13)),INDIRECT($CM$1&$CK$2&$CG$1&":"&$CK$2&$CG$2))-SUMIF(INDIRECT($CM$1&$CK$1&$CG$1&":"&$CK$1&$CG$2),INDIRECT($CM$1&$CK$1&ROW(CF13)),INDIRECT($CM$1&$CK$3&$CG$1&":"&$CK$3&$CG$2)))

---

HOWEVER, THE RANGE CAN BE IN EXCESS OF 60000 ROWS.

AS SUCH I WANT TO AUTOMATE ON VBA TO TAKE SOME OF THE BURDEN OFF.

THUS FAR I HAVE PRODUCED THE FOLLOWING:

---

Sub Macro5()
'
' Macro5 Macro
'
'
Dim PartX As String
Dim PartY As String
Dim PartZ As String

PartX = "=MAX(INDIRECT(R1C91&R2C91&ROW(RC)),INDIRECT(R1C91&R3C89&ROW(RC)))""+""X_X_X())"
PartY = "=SUMIF(INDIRECT(R1C91&R1C89&R1C85&"":""&R1C89&R2C85),INDIRECT(R1C91&R1C89&ROW(RC)),INDIRECT(R1C91&R2C89&R1C85&"":""&R2C89&R2C85))""-""Y_Y_Y())"
PartZ = "=SUMIF(INDIRECT(R1C91&R1C89&R1C85&"":""&R1C89&R2C85),INDIRECT(R1C91&R1C89&ROW(RC)),INDIRECT(R1C91&R3C89&R1C85&"":""&R3C89&R2C85))"

With Sheets(3).Range("CF6:CF10")
.FormulaArray = PartX
.Replace "X_X_X())", PartY
.Replace "Y_Y_Y())", PartZ

End With

End Sub

---

NOTE THAT THE RANGE ABOVE IS JUST A PROXY WHILST I GET THE FORMULA TO WORK - SO ANY ADVICE ON HOW TO LINK THIS TO SOME SORT OF ACTIVE DATA ROW # WOULD BE AWESOME!

FRUSTRATINGLY I STILL GET THE "UNABLE TO SET THE FORMULAARRAY PROPERTY OF THE RANGE CLASS" MESSAGE.

ANY SUGGESTIONS ON IMPROVEMENT WOULD BE MUCH APPRECIATED!

THANKS,
WE2286

 

[steve the fish]

Hi. Im pretty sure im right in saying you need to use A1 reference to do this not R1C1. Try rewriting it that way and come back if still having trouble. Also you wouldnt need the equals sign in partY or partZ. You only need it once.

 

[WE2286]

Hi,

I still get the same error and the debug refers explicitly to the following portion of the formula:

.FormulaArray = PartX

Any further suggestions?

Thanks for your help and the quick reply!

 

[steve the fish]

Ok paste what you have now.

 

[steve the fish]

In fact maybe it would be easier to paste the formula that you need in CF6

 

[WE2286]

My formula is currently:

---

Sub Macro11()
'
' Macro11 Macro
'
'
Dim PartX As String
Dim PartY As String
Dim PartZ As String

PartX = "=MAX(INDIRECT(CM1&CM2&ROW()),INDIRECT(CM1&CK3&ROW()))""+""X_X_X()"
PartY = "SUMIF(INDIRECT(CM1&CK1&CG1&"":""&CK1&CG2),INDIRECT(CM1&CK1&ROW()),INDIRECT(CM1&CK2&CG1&"":""&CK2&CG2))""-""Y_Y_Y()"
PartZ = "SUMIF(INDIRECT(CM1&CK1&CG1&"":""&CK1&CG2),INDIRECT(CM1&CK1&ROW()),INDIRECT(CM1&CK3&CG1&"":""&CK3&CG2))"

With Sheets(3).Range("CF6:CF10")
.FormulaArray = PartX
.Replace "X_X_X()", PartY
.Replace "Y_Y_Y()", PartZ

End With

End Sub

---

As mentioned above the debug cites the yellow portion.

And is your suggestion to copy down the formula from cell CF6 via a macro?

Thanks again for your help!
WE

 

[steve the fish]

No i meant the formula that you have on the worksheet in the cell CF6

 

[WE2286]

=max(indirect($cm$1&$cm$2&row()),indirect($cm$1&$ck$3&row()))+(sumif(indirect($cm$1&$ck$1&$cg$1&":"&$ck$1&$cg$2),indirect($cm$1&$ck$1&row()),indirect($cm$1&$ck$2&$cg$1&":"&$ck$2&$cg$2))-sumif(indirect($cm$1&$ck$1&$cg$1&":"&$ck$1&$cg$2),indirect($cm$1&$ck$1&row()),indirect($cm$1&$ck$3&$cg$1&":"&$ck$3&$cg$2)))

 

[steve the fish]

It doesnt appear to be an array formula as far as i can tell so it can just be entered normally.