All:
I have the following code that runs without errors; however, the results are not what I was expecting.
Here is what is returned as an array: =SUM(IF("Anderson 31-12"=$G$3:$G$226,IF(1/1/2011<=$I$3:$I$226,IF(1/31/2011>=$I$3:$I$226,1,0)*$H$3:$H$226,0)))
The Bold text will not calculate in Excel. Therefore I presume my equation syntax for ForumlaArray is incorrect. But I have yet to figure it out.
The FormulaArray equation is an attempt to duplicate the formula below. It works fine in Excel.
SUM(IF($L3=$G$3:$G$226,IF(M$2<=$I$3:$I$226,IF(EOMONTH(M$2,0)>=$I$3:$I$226,1,0)*$H$3:$H$226,0)))
Any advice on how to get this to work, would be greatly appreciated.
Option Explicit
Sub Macro3()
Dim i As Integer
Dim j As Integer
Dim PadName As String
Dim BegMonthDate As Date 'Beginning of Month
Dim EndMonthDate As Date 'End of Month
i = 3 'Initialize Row Counter
j = 13 'Initialize Column Counter
For j = 13 To 48
'For i = 3 To 65 Commented out for testing
PadName = Cells(i, 12)
BegMonthDate = Cells(2, j)
EndMonthDate = WorksheetFunction.EoMonth(Cells(2, j), 0)
Worksheets(ActiveSheet.Name).Cells(i, j).Select
Selection.FormulaArray = "=SUM(IF(""" & PadName & """=$G$3:$G$226,IF(" & BegMonthDate & "<=$I$3:$I$226,IF(" & EndMonthDate & ">=$I$3:$I$226,1,0)*$H$3:$H$226,0)))"
'Next i
Next j
End Sub
Bookmarks