Coding a SumProduct formula
 

Excel XP, WinXP
I have a number of columns of dates.
I want to get the number of instances (cells) of dates that match specific
months and years, in a specific column.
The sheet formula:
=SUMPRODUCT((MONTH(A1:A100)=1)*(YEAR(A1:A100)=2001 ))
works just fine, but I want to use VBA so the user can input the month,
year, and column.
My code looks like this:
TheCol = Application.InputBox...........
TheMonth = Application.InputBox..............
TheYear = Application.InputBox...............
Set TheRng = Range(Cells(2, TheCol), Cells(Rows.Count, TheCol).End(xlUp))
MsgBox Application.SumProduct((Application.Month(TheRng) = 1) *
(Application.Year(TheRng) = TheYear))

I get the error "Object doesn't support this property or method." on the
last line (MsgBox).
What am I doing wrong? Is it that SumProduct is one of the sheet formulas
that cannot be used in VBA?
Thanks for your time. Otto

Coding a SumProduct formula
 

MsgBox Evaluate("=SUMPRODUCT((MONTH(" & TheRng.Address & ")=" & TheMonth &
")*(YEAR(" & TheRng.Address & ")=" & TheYear & "))")

"Otto Moehrbach" wrote:

Excel XP, WinXP
I have a number of columns of dates.
I want to get the number of instances (cells) of dates that match specific
months and years, in a specific column.
The sheet formula:
=SUMPRODUCT((MONTH(A1:A100)=1)*(YEAR(A1:A100)=2001 ))
works just fine, but I want to use VBA so the user can input the month,
year, and column.
My code looks like this:
TheCol = Application.InputBox...........
TheMonth = Application.InputBox..............
TheYear = Application.InputBox...............
Set TheRng = Range(Cells(2, TheCol), Cells(Rows.Count, TheCol).End(xlUp))
MsgBox Application.SumProduct((Application.Month(TheRng) = 1) *
(Application.Year(TheRng) = TheYear))

I get the error "Object doesn't support this property or method." on the
last line (MsgBox).
What am I doing wrong? Is it that SumProduct is one of the sheet formulas
that cannot be used in VBA?
Thanks for your time. Otto

Coding a SumProduct formula
 

JMB
Thanks for that. I'll try it. Otto
"JMB" wrote in message
...
MsgBox Evaluate("=SUMPRODUCT((MONTH(" & TheRng.Address & ")=" & TheMonth &
")*(YEAR(" & TheRng.Address & ")=" & TheYear & "))")

"Otto Moehrbach" wrote:

Excel XP, WinXP
I have a number of columns of dates.
I want to get the number of instances (cells) of dates that match
specific
months and years, in a specific column.
The sheet formula:
=SUMPRODUCT((MONTH(A1:A100)=1)*(YEAR(A1:A100)=2001 ))
works just fine, but I want to use VBA so the user can input the month,
year, and column.
My code looks like this:
TheCol = Application.InputBox...........
TheMonth = Application.InputBox..............
TheYear = Application.InputBox...............
Set TheRng = Range(Cells(2, TheCol), Cells(Rows.Count, TheCol).End(xlUp))
MsgBox Application.SumProduct((Application.Month(TheRng) = 1) *
(Application.Year(TheRng) = TheYear))

I get the error "Object doesn't support this property or method." on the
last line (MsgBox).
What am I doing wrong? Is it that SumProduct is one of the sheet
formulas
that cannot be used in VBA?
Thanks for your time. Otto