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
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