+ Reply to Thread
Results 1 to 20 of 20

Macro Error #1004

  1. #1
    Registered User
    Join Date
    05-31-2018
    Location
    Michigan
    MS-Off Ver
    2013
    Posts
    7

    Macro Error #1004

    Hello, I am running excel 2013 and get the error 1004 when I try to run my macro. The problem is in the formula highlighted in yellow. I do not know how to fix it.
    Attached Images Attached Images
    Last edited by TheExcelDummy; 06-01-2018 at 03:07 PM.

  2. #2
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    18,813

    Re: Macro Error #1004

    Welcome to the Forum!

    An image attachment has very little value. Just attach the Excel file. It's easier than taking a screenshot first and then attaching that.

    The paper clip icon does not work for attachments. Instead, under the text box where you type your reply click the Go Advanced button. On the next screen scroll down and click on Manage Attachments, which will show a pop-up window to Select and Upload a file. Then close the window.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,726

    Re: Macro Error #1004

    There is character length limit of 255 for .Formula / .FormulaR1C1 if I recall.

    Instead of just using line change "_", try splitting formula string into smaller bits using "&" as well.

    If you need help, post the actual code here using # (code tag), rather than posting image.
    Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.
    ― Robert A. Heinlein

  4. #4
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Macro Error #1004

    The 255 character limit only applies to FormulaArray.

    The formula must be wrong but since we can't see all of it, it's hard to correct!
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  5. #5
    Registered User
    Join Date
    05-31-2018
    Location
    Michigan
    MS-Off Ver
    2013
    Posts
    7

    Re: Macro Error #1004

    Wow! You guys are fast.

    I can't attach the file due to personal info but I will layout the code below.

    Also, the formula works in normal format. I'm not used to seeing a formula in macro format so that may make a difference.

    Here is the code:

    Please Login or Register  to view this content.
    Last edited by 6StringJazzer; 05-31-2018 at 12:32 PM. Reason: code tags

  6. #6
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,726

    Re: Macro Error #1004

    @xlnitwit
    Thanks for the info. I almost never use .Formula, .FormulaArray etc in my codes and had thought all shared same property.

  7. #7
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Macro Error #1004

    @TheExcelDummy

    Please edit your post to use code tags.

    Your formula is indeed invalid.

  8. #8
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    18,813

    Re: Macro Error #1004

    Moderator's note: Please take the time to review our rules. There aren't many, and they are all important. Rule #3 requires code tags. I have added them for you this time because you are a new member. --6StringJazzer

  9. #9
    Registered User
    Join Date
    05-31-2018
    Location
    Michigan
    MS-Off Ver
    2013
    Posts
    7

    Re: Macro Error #1004

    Thank you, sorry about that.

  10. #10
    Registered User
    Join Date
    05-31-2018
    Location
    Michigan
    MS-Off Ver
    2013
    Posts
    7

    Re: Macro Error #1004

    Thank you, where did I go wrong?

  11. #11
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 2016
    Posts
    4,904

    Re: Macro Error #1004

    The second line of the VBA for the formula starts with -3, but there is no RC[ in front of that... also the same line ends in RC[ but the next line does not continue that....
    Please help by:

    Marking threads as closed once your issue is resolved. How? The Thread Tools at the top
    Any reputation (*) points appreciated. Not just by me, but by all those helping, so if you found someone's input useful, please take a second to click the * at the bottom left to let them know

    There are 10 kinds of people in this world... those who understand binary, and those who don't.

  12. #12
    Registered User
    Join Date
    05-31-2018
    Location
    Michigan
    MS-Off Ver
    2013
    Posts
    7

    Re: Macro Error #1004

    I fixed it so the "& _" didn't cut out what I needed, is there a rule with the "& _" that I need to work with because I still get a compile error

    Please Login or Register  to view this content.

  13. #13
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 2016
    Posts
    4,904

    Re: Macro Error #1004

    Painful formula to work through... Aside from some other small issues in the formula, one big thing on your last post is that the 2nd formula line does not start with opening quotes to continue the string, same with row 3, also check the endings of rows 1 and 2. However, try this ammended version:

    Please Login or Register  to view this content.
    Last edited by Arkadi; 05-31-2018 at 03:21 PM.

  14. #14
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 2016
    Posts
    4,904

    Re: Macro Error #1004

    Note that you have an if where you check for Instant approved, and then later you use a lower case "i" (instant approved)
    The formula will be case sensitive so make sure you double-check for that.

  15. #15
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Macro Error #1004

    The = operator in an Excel formula is not case sensitive.

  16. #16
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 2016
    Posts
    4,904

    Re: Macro Error #1004

    Thanks xlnitwit.... My bad then. I always just assumed and never did test that. Appreciate the tip

  17. #17
    Registered User
    Join Date
    05-31-2018
    Location
    Michigan
    MS-Off Ver
    2013
    Posts
    7

    Re: Macro Error #1004

    Thank you so much. What was the determining factor in breaking up the lines. How long until you have to break it up?

  18. #18
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 2016
    Posts
    4,904

    Re: Macro Error #1004

    I like to break them up so that I don't have to scroll across my monitor... but aside from that there is no rule. The breaking up I did had nothing to do with making it work... it was a matter of fixing the quoting within the string. There was a stray comma somewhere, and on the first line it ended with ""frau" & _
    The double quote before frau would make "frau part of the formula, with no quote at the end... since the last quote only completes the string for that row.
    It can be tough to put those together in code... I typically suggest that you first make long formulas like that inside a cell to make sure it is written right, then once working, paste into VBA, add " & _ to break up the lines, and then for any " that is part of the formula (so not the new ones you put in just to break up the code into multiple lines), change it to a double ""

    P.S. Thanks for the rep, and if the issue is resolved, please remember to mark the thread as solved? Thanks in advance

  19. #19
    Registered User
    Join Date
    05-31-2018
    Location
    Michigan
    MS-Off Ver
    2013
    Posts
    7

    Re: Macro Error #1004

    I originally recorded the macro of my typing in the formula but it broke it up and put the "& _" in for me and cut out some pieces, thus creating the "frau" so I will look out for that next time.

    Thank you again.
    Last edited by TheExcelDummy; 06-01-2018 at 03:05 PM.

  20. #20
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 2016
    Posts
    4,904

    Re: Macro Error #1004

    No problem! Glad I could help.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Urgent Excel 2010 Macro error 1004 Method Open of Workbook Failed and Run Time Error 9
    By Jmeetha1985 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-20-2014, 05:33 PM
  2. [SOLVED] Sort Macro Run-time error '1004': Application/Object-defined error.
    By sam1212 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-22-2014, 10:05 AM
  3. Excel Macro Error - Run time error 1004 - Paste method of worksheet class failed
    By kvflynn in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-15-2012, 10:51 AM
  4. Run-time error '1004': Macro error, only when shared
    By Pergo in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-13-2011, 12:57 PM
  5. Macro gives error 1004
    By tuph in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-24-2007, 11:28 PM
  6. [SOLVED] Excel 2003 Macro Error - Runtime error 1004
    By Cow in forum Excel General
    Replies: 2
    Last Post: 06-07-2005, 09:05 AM
  7. naming tab macro error runtime error 1004
    By D in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-27-2005, 10:06 PM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1