PDA

View Full Version : .FormulaArray entry limited to 256 character equation??

skulakowski
03-09-2012, 07:51 AM
I'm entering array equations into cells.
D2 below works.
E2 doesn't--because the equation is more than 256 characters long.

I'll rewrite this equation to make it work but does anybody have a generally applicable clever approach that will side step the 256 character limit? (Excel 2007)

Dim A1 As String
Dim D2 As String
Dim E2 As String

D2 = "=IF(SUMPRODUCT(IF(LoanNo=\$J2,1,0),IF(NewPropAddress=\$R2,1,0)) <10, TEXT(SUMPRODUCT(IF(LoanNo=\$J2,1,0),IF(NewPropAddress=\$R2,1,0)),"" 0""),SUMPRODUCT(IF(LoanNo=\$J2,1,0),IF(NewPropAddress=\$R2,1,0)))& "" | ""&J2"
E2 = "=SUMPRODUCT(IF(LoanNo=\$J2,1,0),IF(NewPropAddress=\$R2,1,0),IF(TradeRisk=""Lower"",1,0))+SUMPRODUCT(IF(LoanNo=\$J2,1,0),IF(NewPropAddress=\$R2,1,0),IF(TradeRis k=""Moderate"",1,0))+SUMPRODUCT(IF(LoanNo=\$J2,1,0),IF(NewPropAddress=\$R2,1,0),IF(TradeRis k=""Higher"",1,0))"

n = 4
If ActiveCell.Offset(0, n).FormulaArray <> D2 Then ActiveCell.Offset(0, n).FormulaArray = D2
n = n + 1
If ActiveCell.Offset(0, n).Formula <> E2 Then ActiveCell.Offset(0, n).Formula = E2

Bob Phillips
03-10-2012, 09:10 AM
No need for array entering

Dim A1 As String
Dim D2 As String
Dim E2 As String

D2 = "=IF(SUMPRODUCT(IF(LoanNo=\$J2,1,0),IF(NewPropAddress=\$R2,1,0))<10," & _
"TEXT(SUMPRODUCT(IF(LoanNo=\$J2,1,0),IF(NewPropAddress=\$R2,1,0)),"" 0"")," & _
"SUMPRODUCT(IF(LoanNo=\$J2,1,0),IF(NewPropAddress=\$R2,1,0)))& "" | ""&J2"
E2 = "=SUMPRODUCT(IF(LoanNo=\$J2,1,0),IF(NewPropAddress=\$R2,1,0),IF(TradeRisk=""Lower"",1,0))" & _
"+SUMPRODUCT(IF(LoanNo=\$J2,1,0),IF(NewPropAddress=\$R2,1,0),IF(TradeRisk=""Moderate"",1,0))" & _
"+SUMPRODUCT(IF(LoanNo=\$J2,1,0),IF(NewPropAddress=\$R2,1,0),IF(TradeRisk=""Higher"",1,0))"

n = 4
If ActiveCell.Offset(0, n).Formula <> D2 Then ActiveCell.Offset(0, n).Formula = D2
n = n + 1
If ActiveCell.Offset(0, n).Formula <> E2 Then ActiveCell.Offset(0, n).Formula = E2