+ Reply to Thread
Results 1 to 10 of 10

Runtime error 1004 - Application Defined or Object Defined Error

  1. 05-18-2012, 07:22 PM #1
    ljuarez714
    ljuarez714 is offline
    Registered User
    Join Date
    05-18-2012
    Location
    OC, California
    MS-Off Ver
    Excel 2010
    Posts
    13

    Runtime error 1004 - Application Defined or Object Defined Error

    Hello,

    I get the runtime error in excel 2010 when running a macro for an IF statement/formula. The formula is:

    =IF(AND(A8="4050.000",C8>0),"Over Budget: Line item not budgeted, however late charges billed and collected per the Billing & Collection Policy.",(IF(AND(A8="4050.000",C8<0),"Under Budget: Payment to xx for 50% of collected late fees from prior fiscal years.",(IF(AND(A8="",C8>1),"",(IF(A8="","",(IF(AND(E8>=100,F8>="105.00%"),"Over Budget:",(IF(F8="100.00%","No Variance",(IF(AND(C8<=0,D8>0),"Under Budget: No expense incurred",(IF(AND(C8>0,D8=0),"Over Budget: Line item not budgeted",(IF(AND(F8<"95.00%",E8<=-100),"Under Budget:",(IF(F8="100.00%","No Variance",(IF(AND(F8="0.00%",G8>1),"No Variance",(IF(AND(OR(F8<"100.00%",F8>"95.00%"),OR(E8>-100,E8<100)),"Under Budget: No significant variance","Over Budget: No significant variance")))))))))))))))))))))))

    When I hit debug, it takes me to the VB window and highighlights the formula. I don't know anything about programming so please try to explain in laymans terms for me. The IF statement is 55 words if that's any help.

    Here is the VB code:
    Please Login or Register  to view this content.
    Last edited by davesexcel; 05-18-2012 at 07:31 PM. Reason: Code tags required when showing VBA code, please read the forum rules
    Reply With QuoteRegister To Reply
  2. 05-18-2012, 07:34 PM #2
    davesexcel
    davesexcel is offline
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,523

    Re: Runtime error 1004 - Application Defined or Object Defined Error

    For a line continuation you need to use a space and the underscore line " _" do not use "&"
    ,
    Xlorate.com, Selection Codes, YouYube Channel
    Reply With QuoteRegister To Reply
  3. 05-19-2012, 03:53 PM #3
    ljuarez714
    ljuarez714 is offline
    Registered User
    Join Date
    05-18-2012
    Location
    OC, California
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Runtime error 1004 - Application Defined or Object Defined Error

    Quote Originally Posted by davesexcel View Post
    For a line continuation you need to use a space and the underscore line " _" do not use "&"
    VB automatically generates the "&" in the VB code. All I do is in the excel workbook is:

    hit record macro, paste the IF formula into a cell J8(the formula is copied from a word doc), I drag J8 down to J100 (to copy formula), then a couple of other small steps and I stop recording the macro.

    When I open a workbook with info on it, I go to run the macro and I get the error. I tried removing the "&" in the VB window but then I get another error when I do so:

    Compile Error:Expected line number or label or statement or end of statement
    Reply With QuoteRegister To Reply
  4. 05-19-2012, 04:34 PM #4
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: Runtime error 1004 - Application Defined or Object Defined Error

    Try this:

    Please Login or Register  to view this content.
    Last edited by shg; 05-19-2012 at 04:37 PM.
    Entia non sunt multiplicanda sine necessitate
    Reply With QuoteRegister To Reply
  5. 05-21-2012, 01:08 PM #5
    ljuarez714
    ljuarez714 is offline
    Registered User
    Join Date
    05-18-2012
    Location
    OC, California
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Runtime error 1004 - Application Defined or Object Defined Error

    The good news is that I don't get any error messages anymore. However,I need the formula to start referencing cells in row 8 which I will drag down from (J8:J100). The formula you gave me above starts referencing from row 11. Also, the code you suggested was missing some the following condition "Over Budget: No significant Variance". Due to this, the code/macro is not generating the correct results. I was trying to add the missing condition by replicating the suggested code, but I'm not familiar with vb or programming so i got confused.

    One more question. When I ran the macro and clicked on cells that had the IF formula, the "(" was missing in the subsequent IF statements, here is in example: =IF(x,x,IF(x,x,x)).

    Sorry if I confused you with my non vb programming speak.
    Last edited by Cutter; 07-31-2012 at 06:19 PM. Reason: Removed whole post quote
    Reply With QuoteRegister To Reply
  6. 05-21-2012, 01:51 PM #6
    ljuarez714
    ljuarez714 is offline
    Registered User
    Join Date
    05-18-2012
    Location
    OC, California
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Runtime error 1004 - Application Defined or Object Defined Error

    From the code sgh suggested, I was able to figure out how to start referencing from row 8, I changed R3 to R0.

    The only thing I need now is help on adding the final statement to the end of the IF formula in VB: IF(AND(OR(F8<"100.00%",F8>"95.00%"),OR(E8>-100,E8<100)),"Under Budget: No significant variance","Over Budget: No significant Variance"

    Because this condition is missing, the formula returns a "0" where this statement should generate. I attempted to add it in as ""xx"" but it still wouldn't generate.
    Reply With QuoteRegister To Reply
  7. 05-21-2012, 02:22 PM #7
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: Runtime error 1004 - Application Defined or Object Defined Error

    Hmm.

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  8. 05-22-2012, 04:19 PM #8
    ljuarez714
    ljuarez714 is offline
    Registered User
    Join Date
    05-18-2012
    Location
    OC, California
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Runtime error 1004 - Application Defined or Object Defined Error

    SGH,
    The above worked, but I have another issue. I apologize in advance if this should be in a new post.
    I added one more condition that was missing at the end of the formula: IF(AND(F8<"95.00%",E8<0),"Under Budget: No significant Variance","Over Budget: No significant variance")))))))))))))))))))))))

    When I run the macro it gives me the same Run Time error. When I go to debug, it highlights the .FormulaR1C1 = sFrm in yellow. Here is the code I have in VB:

    Please Login or Register  to view this content.
    Thanks again for all your help everyone, when this works as it should it will save our department 117 hours in labor every year!!!
    Last edited by Cutter; 07-31-2012 at 06:20 PM. Reason: Removed whole post quote and added code tags
    Reply With QuoteRegister To Reply
  9. 07-31-2012, 04:27 PM #9
    bhangra
    bhangra is offline
    Registered User
    Join Date
    07-27-2012
    Location
    usa
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Runtime error 1004 - Application Defined or Object Defined Error

    I get the same error on the following line "Set ts = .Find(TestCaseName, LookIn:=xlValues, lookat:=xlWhole)" when I run it the first time and try to go back to the related sheet then I get error 1004 message. Please help.


    Please Login or Register  to view this content.
    Last edited by Cutter; 07-31-2012 at 06:21 PM. Reason: Added code tags
    Reply With QuoteRegister To Reply
  10. 07-31-2012, 06:23 PM #10
    Cutter
    Cutter is offline
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: Runtime error 1004 - Application Defined or Object Defined Error

    Hello bhangra, and welcome to the forum.

    Unfortunately you have inadvertently broken two of the forum rules. Please read the following and make the necessary change. Thanks.

    Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

    And

    Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1