Answered by:
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 RunTime 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 nonarray 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 nonarray 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