+ Reply to Thread
Results 1 to 11 of 11

VBA for Inserting Formula returning Run-time error '1004'

  1. 12-18-2013, 01:05 PM #1
    blwager is offline
    Registered User
    Join Date
    10-10-2013
    Location
    California, USA
    MS-Off Ver
    Excel 2003
    Posts
    6

    VBA for Inserting Formula returning Run-time error '1004'

    Hello,

    I'm attempting to auto-fill a formula that has been converted to VBA.
    Rather than going through the tedius method of manually rewriting the formula to be filled via VBA, I used the auto-record method.

    Trouble is, rather than running smoothly it is returning the following error before even reaching the auto-fill portion of code:
    "Run-time error '1004':
    Application-defined or object-defined error"

    All my single-line formulas are working just fine, but the following two are getting the same error (I recognize the 2nd is a mammoth. If the problem with the 1st is fixed, I should be able to repair the 2nd on my own):

    Please Login or Register  to view this content.
    Any help will be greatly appreciated. And before anyone offers it, I did double-check that it' susing the ..." & _" syntax for long vba code. I couldn't find anything more for multiple lines of code in a formula.
    Last edited by blwager; 12-18-2013 at 04:42 PM. Reason: code tags
    Reply With QuoteRegister To Reply
  2. 12-18-2013, 01:16 PM #2
    6StringJazzer is offline
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS 365 Family 64-bit 2502
    Posts
    26,789

    Re: VBA for Inserting Formula returning Rune-time error '1004'

    If I "glue" together the end of your first formula that is split by the continuation, I get this

    SUMIFS('Verizon Cells & ACards'!C14n Cells & ACards'!C3,'Verizon Cells & ACards'!RC3)

    I think you are missing something in here. Maybe it's supposed to look like the SUMIFS call earlier in the same formula:

    SUMIFS('Verizon Cells & ACards'!C14,'Verizon Cells & ACards'!C3,'Verizon Cells & ACards'!RC3)
    Jeff
    | | |·| |·| |·| |·| | |:| | |·| |·|
    Read the rules
    Use code tags to [code]enclose your code![/code]
    Reply With QuoteRegister To Reply
  3. 12-18-2013, 01:17 PM #3
    blwager is offline
    Registered User
    Join Date
    10-10-2013
    Location
    California, USA
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: VBA for Inserting Formula returning Rune-time error '1004'

    You're right! It is cutting off part of the formula in the recording.
    Still having a problem though.
    Last edited by blwager; 12-18-2013 at 02:17 PM.
    Reply With QuoteRegister To Reply
  4. 12-18-2013, 01:17 PM #4
    6StringJazzer is offline
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS 365 Family 64-bit 2502
    Posts
    26,789

    Re: VBA for Inserting Formula returning Rune-time error '1004'

    You have a similar problem in the second one

    VLOOKUP('Cost by Decl'!R1C2,riods,5)

    VLOOKUP('Cost by Decl'!R1C2,BillingPeriods,5)
    Reply With QuoteRegister To Reply
  5. 12-18-2013, 01:18 PM #5
    6StringJazzer is offline
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS 365 Family 64-bit 2502
    Posts
    26,789

    Re: VBA for Inserting Formula returning Rune-time error '1004'

    I see this every time you have a line continuation.
    Reply With QuoteRegister To Reply
  6. 12-18-2013, 01:21 PM #6
    blwager is offline
    Registered User
    Join Date
    10-10-2013
    Location
    California, USA
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: VBA for Inserting Formula returning Rune-time error '1004'

    Yeah, very odd that Excel would cut code in its own recording process. I didn't even consider it.
    Last edited by blwager; 12-18-2013 at 02:17 PM.
    Reply With QuoteRegister To Reply
  7. 12-18-2013, 01:45 PM #7
    blwager is offline
    Registered User
    Join Date
    10-10-2013
    Location
    California, USA
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: VBA for Inserting Formula returning Rune-time error '1004'

    I modified the big one and found it still was having problems. So I tested the small one and it still had the problem too.
    Here's the current version of the small one, error is still the same as before:
    Please Login or Register  to view this content.
    Last edited by blwager; 12-18-2013 at 03:51 PM.
    Reply With QuoteRegister To Reply
  8. 12-18-2013, 03:49 PM #8
    6StringJazzer is offline
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS 365 Family 64-bit 2502
    Posts
    26,789

    Re: VBA for Inserting Formula returning Rune-time error '1004'

    Still missing something after combining lines

    Please Login or Register  to view this content.
    I also need to remind you to use code tags in all code. Because the code is formula intensive I forget we are still talking about VBA code here. I have fixed it for you because you're a new user but please take the time to review our rules. There aren't many, and they are all important.

    Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

    Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

    Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here
    Reply With QuoteRegister To Reply
  9. 12-18-2013, 03:54 PM #9
    blwager is offline
    Registered User
    Join Date
    10-10-2013
    Location
    California, USA
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: VBA for Inserting Formula returning Run-time error '1004'

    Aye aye.
    Somebody with a similar problem used chr(34) to sub the " marks, but the fix didn't seem to help me. Still scanning the internet @_@
    Reply With QuoteRegister To Reply
  10. 12-18-2013, 04:42 PM #10
    blwager is offline
    Registered User
    Join Date
    10-10-2013
    Location
    California, USA
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: VBA for Inserting Formula returning Run-time error '1004'

    Found a fix: http://www.excelforum.com/excel-prog...ned-error.html

    Reformatted the large code, and found it works just fine. Broke the code more and added the "& vblf &" syntax, which to be frankly honest I have no idea what does. I just am glad it is working! 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  11. 12-18-2013, 08:50 PM #11
    6StringJazzer is offline
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS 365 Family 64-bit 2502
    Posts
    26,789

    Re: VBA for Inserting Formula returning Run-time error '1004'

    vbCrLf is a built-in VBA constant that means "carriage return, line feed". It adds a line break in a text output stream. (The terminology is bit archaic; it goes back to the days of the teletype when they had to transmit one character to return the print head back to the left, and another character to roll the paper up one line.)
    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)

Similar Threads

  1. HELP. I changed my sheet name, and now I keep getting rune time errors!
    By Nitefox in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 1
    Last Post: 09-21-2013, 06:16 PM
  2. vba code returning Run Time Error 1004 method 'undo' of object
    By Ironman in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-24-2012, 08:17 PM
  3. Run Time Error 1004 pasting formula into worksheet using VBA.
    By welchs101 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-09-2012, 09:42 PM
  4. Excel 2007 : Run Time Error 1004 when inserting formula
    By grsnipe in forum Excel General
    Replies: 1
    Last Post: 09-23-2011, 10:35 AM
  5. worksheetfunction.vlookup returning run-time error (1004)
    By Mervil in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-19-2010, 01:41 AM

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