| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
runtime error 424 'object required'
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... |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
runtime error 424 'object required'
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
runtime error 424 'object required'
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
runtime error 424 'object required'
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
runtime error 424 'object required'
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
runtime error 424 'object required'
Works perfectly...thanks Jacod and Dave...i also see the problem with my 2nd
code..I was nesting too many times it looks like. "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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
runtime error 424 'object required'
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
runtime error 424 'object required'
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. |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
runtime error 424 'object required'
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. Lman wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Runtime error '424': Object Required | Excel Discussion (Misc queries) | |||
| Help with runtime error 424 Object required | Excel Programming | |||
| Runtime error 424 object required on Set Statement | Excel Programming | |||
| Runtime error 424 - Object required | Excel Programming | |||
| runtime error ....object required | Excel Programming | |||
Linear Mode
