well i thought this had been fixed, however, the result that this displays is
not correct.

Basically i am trying to get this code to check AM1:AM30000 for a date that
falls between 2 dates (8/1/09 and 9/1/09 for example) then add up the numbers
in BN1:BN3000 for any rows containing this date. Not sure if the code i wrote
(with help of course) is wrong or if i am using the wrong function to do what
i need.

here is what i have so far..

Dim Res1 As Integer
Res1 =
Worksheets("sheet1").Evaluate("=sumproduct(--(am1:am30000=date(2009,08,01)),--(am1:am30000date(2009,09,01)),BN1:BN30000)")
MsgBox Res1
End Sub

thanks

"Jacob Skaria" wrote:

Try the below

Dim Res1 As Integer
Res1 =
Worksheets("sheet1").Evaluate("=sumproduct(--(am1:am30000<=date(2009,08,01)),--(am1:am30000date(2008,9,1)),BN1:BN30000)")
MsgBox Res1

Try the above...Review your date conditions...
=date(2009,08,01) and <date(2008,9,1) is not a valid date range

If this post helps click Yes
---------------
Jacob Skaria


"Lman" wrote:

Hi Dave, thanks for your prompt reply, I have modified the code to the
following and get the 'type mismatch' error.

Dim Res1 As Integer
Res1 =
Worksheets("sheet1").Evaluate("=sumproduct((--(am1:am30000=date(2009,08,01)),--(am1:am30000<date(2008,9,1)),BN1:BN30000)))")
MsgBox Res1


note: I had tried just specifing the worksheet with my old sumproduct code
but still got the object required error.



"Dave Peterson" wrote:

Ps. I should have been more clear on this portion:

(am1:am30000=""=08/01/2009"")

You're mixing the =sumif() style of comparing strings with a plain old
comparison (using =).

Dave Peterson wrote:

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

--

Dave Peterson