runtime error 424 'object required'

[Lman]

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]

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.)

 

[Dave Peterson]

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 =).

 

[Lman]

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.

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 =).

 

[Jacob Skaria]

Try the below

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

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

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.

 

[Lman]

Works perfectly...thanks Jacod and Dave...i also see the problem with my 2nd
code..I was nesting too many times it looks like.

Try the below

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

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

 

[Lman]

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:am30000>date(2009,09,01)),BN1:BN30000)")
MsgBox Res1
End Sub

thanks

Try the below

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

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

 

[Lman]

I can get it to work using the following code

Dim Res1 As Long
Dim Res2 As Long
Res1 =
Worksheets("sheet1").Evaluate("=sumproduct(--(am1:am30000>=date(2009,08,01)),BN1:BN30000)")
Res2 =
Worksheets("sheet1").Evaluate("=sumproduct(--(am1:am30000>=date(2009,09,01)),BN1:BN30000)")
MsgBox Res1 - Res2


however i can't seem to get it to combine in one statement.

 

[Dave Peterson]

If you put the formula in an empty cell in sheet1, does it work?

If yes, then try that formula in code.

If you can't get the code to work right, share the formula you got working in
the worksheet cell.

You may want to try the other suggestion, too.