Coding a SumProduct formula

O

Otto Moehrbach

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
 
J

JMB

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

Otto Moehrbach

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top