Consulting

Results 1 to 2 of 2

Thread: .FormulaArray entry limited to 256 character equation??

  1. #1

    .FormulaArray entry limited to 256 character equation??

    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)


    [vba]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=""L ower"",1,0))+SUMPRODUCT(IF(LoanNo=$J2,1,0),IF(NewPropAddress=$R2,1,0),IF(Tr adeRisk=""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).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
    [/vba]

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,275
    Location
    No need for array entering

    [vba]


    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=""L ower"",1,0))" & _
    "+SUMPRODUCT(IF(LoanNo=$J2,1,0),IF(NewPropAddress=$R2,1,0),IF(TradeRisk=""M oderate"",1,0))" & _
    "+SUMPRODUCT(IF(LoanNo=$J2,1,0),IF(NewPropAddress=$R2,1,0),IF(TradeRisk=""H igher"",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
    [/vba]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •