VBA in Access to Create a FormulaArray in Excel

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?

Answer 1

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)

Answer 2

It worked!  Thanks Hans!