I would guess that you want to check a specific worksheet for those ranges:

worksheets("sheetnamehere").evaluate("sumproduct(. ..

ps.

It looks like you're checking for a date in a range. I think that using =date()
is less ambiguous:

....(--(am1:am30000=date(2009,08,01)),--(am1:am30000<date(2008,9,1)),

or even
....(--(text(am1:am30000,""yyyymm"")=""200808""), ...

(watch the double quotes. I didn't test the syntax.)

Lman wrote:

i am getting the above error when trying to run the following code

Dim Res1 As Integer
Res1 =
Worksheet.Evaluate("=sumproduct(--(am1:am30000=""=08/01/2009""),--(am1:am30000=""<09/01/2009""),(BN1:BN3000))")
MsgBox Res1

I am using excel 2007...

--

Dave Peterson