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

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

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