PDA

View Full Version : Unable to set FormulaArray Property of the Range Class - Using Replace Function



zion11
03-03-2014, 06:24 PM
I'm looking to loop a comparison code. I'm using dynamic referencing (using x and y) to find maximum values for specific time intervals. The code works on a cell to cell basis, meaning if I input the formula and change the cell referencing manually then the equation will give the desired results. However when I attempted to create a VBA code to speed up the process I kept getting a 1004 Unable to set FormulaArray Property of the Range Class error, I later figured out that the Formula Array function is limited to a certain number of characters so I split up my function into 3 different string formulas. Now the replace function does NOT work and I have run out of ideas.

This is the output:=MAX(MAX(IF((TEXT(E2,"mmyyyy")=TEXT($A$2:$A$616070,"mmyyyy"))*(ABS(H2-$D$2:$D$616070)>0.95833333333394),$B$2:$B$616070)), X_X Formula_Part_3 )

Thanks for any help in advance.!




Sub fillout()






Dim x As Integer
Dim y As Integer
Dim Formulapart1 As String
Dim Formulapart2 As String
Dim Formulapart3 As String




Formulapart1 = "=MAX(MAX(IF((TEXT(R[" & x & "]C[-12],""mmyyyy"")=TEXT(R2C1:R616070C1,""mmyyyy""))*(ABS(R[" & y & _
"]C[-9]-R2C4:R616070C4)>0.95833333333394),R2C2:R616070C2)), X_X Formula_Part_3 )"

Formulapart2 = "MAX(IF((TEXT(R[" & (x + 1) & "]C[-12],""mmyyyy"")=TEXT(R2C1:R616070C1,""mmyyyy""))*(ABS(R[" & y & _
"]C[-9]-R2C4:R616070C4)>0.95833333333394),R2C2:R616070C2)),"
Formulapart3 = "MAX(IF((TEXT(R[" & (x + 11) & "]C[-12],""mmyyyy"")=TEXT(R2C1:R616070C1,""mmyyyy""))*(ABS(R[" & y & _
"]C[-9]-R2C4:R616070C4)>0.9533333333394),R2C2:R616070C2))"




x = 11
For y = 2 To 4


With Range("Q" & y)
.FormulaArray = Replace(Formulapart1, "X_X", Formulapart2)
.Replace "X_X", Formulapart2
'.Replace "Formula_Part_3", Formulapart3

End With

x = x + 11
Next y
End Sub

Kenneth Hobs
03-04-2014, 03:05 PM
Welcome to the forum!

Please use code tags for code.

IF you can post a simple example workbook with before and after sheets, it helps us help you more readily. I am wondering if maybe a non-formula array would be better. In anycase, and example file might make your needs more clear to me.

Paul_Hossler
03-05-2014, 08:24 PM
MsgBox Len(Replace(Formulapart1, "X_X", Formulapart2))


.FormulaArray = Replace(Formulapart1, "X_X", Formulapart2)
.Replace "X_X", Formulapart2


I'm not good with RC notation and long WS formulas, but if I read this correctly, the length of the Replace ( ) output is still 285 characters so I'm not sure if Replace accomplishes anything by using such long formula pieces.

Maybe breaking it into even smaller pieces and doing the .Replace multiple times would work

http://www.pcreview.co.uk/forums/formulaarray-limit-255-chars-workarounds-exist-t3848527.html

http://dailydoseofexcel.com/archives/2005/01/10/entering-long-array-formulas-in-vba/


Paul

zk69
11-04-2016, 06:00 AM
Hello
I have similar problem with Excel 2010 VBA code.


I got an error message (... error 1004 - unable to set the formulaarray property of the range class) when I try to use the following VBA code.


I defined three constants:


Const Formula1_Valid = "=IF(AND(D38<E38; D39<E39; D40<E40); ""VALID""; ""NOTVALID"")"
Const Formula2_Valid = "=IF(AND(D38<E38; D39<E39; D40<E40; F38<G38; F39<G39; F40<G40); ""VALID""; ""NOTVALID"")"
Const Formula3_Valid = "=IF(AND(D38<E38; D39<E39; D40<E40; F38<G38; F39<G39; F40<G40; H38<I38; H39<I39; H40<I40); ""VALID""; ""NOTVALID"")"


When a certain event occurs (If Target.Address = "$G$5" Then...) I would like to put a formula into a cell E45.


I try to use the following instruction: Sheet1.Range("E45").FormulaArray = Formula1_Valid


What is the problem with this code?
It works if I use the following constant: =SUM(F38:G38)
The cell E45 hasn"t been merged. The formula doesn't exceed 255 Characters.

Thanks in advance.