none
VBA in Access to Create a FormulaArray in Excel RRS feed

  • Question

  • I have a VBA that creates four spreadsheets that are identical but is for four different types of work.  The fifth spreadsheet uses a FormulaArray statement as follows:

    XLSheet.Range("A" & mrow).FormulaArray="=Sum(IF($AB3:$AB999=$T" & mrow & ",'Sheet1'!$Q3:$Q999,0)) + SUM(IF($AB3:AB999=$T" & mrow & ",'Sheet2'!$Q3:$Q999,0)) + SUM(IF($AB3:AB999=$T" & mrow & ",Sheet3!$Q3:$Q999,0))

    Now up to this point it works but when I add the fourth spreadsheet I get the Run-Time Error '1004' Unable to set the FormulaArray property of the Range Class.

    Column T is the value that is the same for all five spreadsheets.

    I can go into the Excel cell and manually add the fourth spreadsheet.  The problem occurs when the fourth spreadsheet is added.  I can change 'Sheet3' to 'Sheet4' and it will work but I can't add the fourth spreadsheet to the formula.  Is there a memory problem here?

    Thursday, July 26, 2018 7:58 PM

Answers

  • You can use SUMIF in a non-array formula:

        xlSheet.Range("A" & mrow).Formula = _
            "=SUMIF(AB3:AB999,$T" & mrow & ",'Sheet1'!Q3:Q999)" & _
            "+SUMIF(AB3:AB999,$T" & mrow & ",'Sheet2'!Q3:Q999)" & _
            "+SUMIF(AB3:AB999,$T" & mrow & ",'Sheet3'!Q3:Q999)" & _
            "+SUMIF(AB3:AB999,$T" & mrow & ",'Sheet4'!Q3:Q999)"

    PS: AB3:AB999 wil be on the sheet XLSheet, are you sure it shouldn't be 'Sheet1'!AB3:AB999, 'Sheet2'!AB3:AB999 etc.?


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Arkansas Lady Thursday, July 26, 2018 10:49 PM
    Thursday, July 26, 2018 9:31 PM
  • It worked!  Thanks Hans!
    • Marked as answer by Arkansas Lady Thursday, July 26, 2018 10:49 PM
    Thursday, July 26, 2018 10:49 PM

All replies

  • You can use SUMIF in a non-array formula:

        xlSheet.Range("A" & mrow).Formula = _
            "=SUMIF(AB3:AB999,$T" & mrow & ",'Sheet1'!Q3:Q999)" & _
            "+SUMIF(AB3:AB999,$T" & mrow & ",'Sheet2'!Q3:Q999)" & _
            "+SUMIF(AB3:AB999,$T" & mrow & ",'Sheet3'!Q3:Q999)" & _
            "+SUMIF(AB3:AB999,$T" & mrow & ",'Sheet4'!Q3:Q999)"

    PS: AB3:AB999 wil be on the sheet XLSheet, are you sure it shouldn't be 'Sheet1'!AB3:AB999, 'Sheet2'!AB3:AB999 etc.?


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Arkansas Lady Thursday, July 26, 2018 10:49 PM
    Thursday, July 26, 2018 9:31 PM
  • It worked!  Thanks Hans!
    • Marked as answer by Arkansas Lady Thursday, July 26, 2018 10:49 PM
    Thursday, July 26, 2018 10:49 PM