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" <(E-Mail Removed)> wrote in message
news:CD688910-F8BF-4C1B-A2E5-(E-Mail Removed)...
> 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
>>
>>
>>