Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: FormulaArray in VBA

  1. #1
    New Member
    Join Date
    Oct 2010
    Posts
    49
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default FormulaArray in VBA

    Hi I keep getting a "run time error 1004 unable to set the formulaarray property of the range class" at this point in my VBA...I can't seem to figure out why:

    Code:
    Set wsout = ActiveSheet
    
    wsout.Range("BS59").FormulaArray = "=IFERROR((INDEX(EC$2:EC$9999,MATCH(1,(OFFSET(A$1,MATCH(BO59,AE$2:AE$199,0),0)=EA$2:EA$9999)*(BU$1=EB$2:EB$9999),0))/INDEX(EC$2:EC$9999,MATCH(1,(OFFSET(A$1,MATCH(BO59,AE$2:AE$199,0),0)=EA$2:EA$9999)*(EOMONTH(BU$1,-1)=EB$2:EB$9999),0)))-1,"""")"
    I read about the character limit of 255, but from what I see this is only 241 so doesn't seem to be that, any help would be appreciated!

  2. #2
    MrExcel MVP
    Moderator
    Scott Huish's Avatar
    Join Date
    Mar 2004
    Location
    Oregon
    Posts
    19,728
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    4 Thread(s)

    Default Re: FormulaArray in VBA

    You can't enter that formula as an array formula manually in the spreadsheet. Does it give you the correct result if you change FormulaArray to Formula?
    Office 2010/365

  3. #3
    New Member
    Join Date
    Oct 2010
    Posts
    49
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: FormulaArray in VBA

    Quote Originally Posted by Scott Huish View Post
    You can't enter that formula as an array formula manually in the spreadsheet. Does it give you the correct result if you change FormulaArray to Formula?
    changing ,"""") at the end of ,0) makes it work fine...even when I use this in VBA it doesnt work

  4. #4
    MrExcel MVP
    Moderator
    Scott Huish's Avatar
    Join Date
    Mar 2004
    Location
    Oregon
    Posts
    19,728
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    4 Thread(s)

    Default Re: FormulaArray in VBA

    I don't think you're understanding. Using FormulaArray on a formula that can't be entered as an array formula will give an error.
    Office 2010/365

  5. #5
    New Member
    Join Date
    Oct 2010
    Posts
    49
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: FormulaArray in VBA

    Quote Originally Posted by Scott Huish View Post
    I don't think you're understanding. Using FormulaArray on a formula that can't be entered as an array formula will give an error.
    I guess so because the formula I provided seems fine to be entered as an array formula in excel itself...bar what I noted above clearly, what am I missing here?

  6. #6
    MrExcel MVP
    Moderator
    Scott Huish's Avatar
    Join Date
    Mar 2004
    Location
    Oregon
    Posts
    19,728
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    4 Thread(s)

    Default Re: FormulaArray in VBA

    Sorry, my apologies.
    Office 2010/365

  7. #7
    MrExcel MVP
    Moderator
    Scott Huish's Avatar
    Join Date
    Mar 2004
    Location
    Oregon
    Posts
    19,728
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    4 Thread(s)

    Default Re: FormulaArray in VBA

    I did find this information though, and it probably is the cause of your problem. FormulaArray can be used with A1-style notation, but is meant to be used with R1C1 notation.
    The R1C1 equivalent formula must be less than 255 characters, and your A1 style formula is already at 240 characters.

    Working With Range.FormulaArray In VBA | RAD Excel

    If you can explain what you are trying to do with your formula exactly, perhaps someone can come up with a shorter formula.
    Office 2010/365

  8. #8
    New Member
    Join Date
    Jan 2013
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: FormulaArray in VBA

    I too am trying to enter a FormulaArray in VBA. im getting the same error '1004' and I believe I have just over 220 characters. when I hover over each of the components in the formula everything seems to be working correctly except for the syntax of combining my 5 variable criteria. Essentially ive named five variables pc1 - pc5 and I want to do a sumif in the same criteria range (single column) for any/all of these 5 variables.

    Select Case Left(wf.Trim(wf.Clean(LCase(cl))), 2)
    Case "bp", "p1": pc1 = Left(wf.Trim(wf.Clean(LCase(cl))), 10)
    pc2 = Left(wf.Trim(wf.Clean(LCase(cl))), 7) & Mid(cl, 12, 3)
    pc3 = Left(wf.Trim(wf.Clean(LCase(cl))), 7) & Mid(cl, 16, 3)
    pc4 = Left(wf.Trim(wf.Clean(LCase(cl))), 7) & Mid(cl, 20, 3)
    pc5 = Left(wf.Trim(wf.Clean(LCase(cl))), 7) & Mid(cl, 24, 3)

    For Each cl1 In ws_port.Range(Cells(cl.Row, Range("curr_zfin").Offset(, -5).Column), Cells(cl.Row, Range("curr_zfin").Column))
    cl1.FormulaArray = "=Sum(SumIFs('[ZFIN.xlsx]" & Cells(Range("curr_zfin").Row, cl1.Column) & "'!$D$7:$D$2500,'[ZFIN.xlsx]" & Cells(Range("curr_zfin").Row, cl1.Column) & "'!$B$7:$B$2500,{" & pc1 & "," & pc2 & "," & pc3 & "," & pc4 & "," & pc5 & "}))"
    cl1.Value = cl1.Value * -1
    cl1.NumberFormat = "_(* #,##0_);_(* (#,##0);_(* "" - ""??_);_(@_)"
    Next cl1

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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