+ Reply to Thread
Results 1 to 8 of 8

Run-time error '1004'

  1. #1
    Registered User
    Join Date
    05-02-2016
    Location
    USA
    MS-Off Ver
    2013
    Posts
    35

    Run-time error '1004'

    I am using a variable to reference the name of a sheet in my workbook. It works in one place, but not in another. I know (KNOW!) that I am missing something simple, but I'm not seeing it. Below are the appropriate lines, copied exactly from my VBA in case there are typos (but the error doesn't happen in compile, only when running).

    Please Login or Register  to view this content.

    What am I missing? I get "Run-time error '1004': Application-defined or object-defined error" when I try to run the debug.print line using the variables. I've checked that Var1 and Var2 are holding the right values, that the cell isn't blank (just in case).

    I tried the following, but none of these works, either:
    Debug.Print Worksheets(sourcesheet).Cells(Var1, Var2).Value
    Debug.Print ThisWorkbook.Worksheets(sourcesheet).Cells(Var1, Var2)
    Debug.Print ThisWorkbook.Worksheets(sourcesheet).Cells(Var1, Var2).Value

    I thought possibly it could be an issue of the subroutine being placed in the wrong module, but all of my VBA is in one Module (Module1, as I wasn't feeling creative).

    None of my cells are protected, so that's not the cause.

    Thanks for any help you can give me.
    Last edited by dhunton; 12-26-2016 at 01:11 AM.

  2. #2
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: Run-time error '1004'

    Moderation removed. Code Tags are added.
    Last edited by Winon; 12-26-2016 at 07:18 PM.
    Please consider:

    Be polite. Thank those who have helped you. Then Click on the star icon in the lower left part of the contributor's post and add Reputation. Cleaning up when you're done. If you are satisfied with the help you have received, then Please do Mark your thread [SOLVED] .

  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: Run-time error '1004'

    Suggest you post a sample workbook. The code looks as though it should be ok.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  4. #4
    Registered User
    Join Date
    05-02-2016
    Location
    USA
    MS-Off Ver
    2013
    Posts
    35

    Re: Run-time error '1004'

    I've cut this down to the bits that I feel might be contributing, and taken out anything that is private data. (The entire VBA code is, at present, and taking out blank lines and comment lines, almost 2,000 lines long, but this MUCH shorter version still has the problem. )

    Any suggestions or questions that head me in the right direction would be appreciated.
    Attached Files Attached Files

  5. #5
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,570

    Re: Run-time error '1004'

    Dim SourceDate As String
    Dim SourceTime As String

    ----------------------------------
    targetsheet.Cells(LoopCounter, RFDTC) = Worksheets(datasheet).Cells(SourceRow, SourceDate) ' type Long required
    targetsheet.Cells(LoopCounter, RFTC) = Worksheets(datasheet).Cells(SourceRow, SourceTime)

    ----------------------------------
    SourceDate (string) = FDTC (Integer/Long)

    * Your Integers should be reDim'd as Long
    Last edited by protonLeah; 12-30-2016 at 12:36 AM.
    Ben Van Johnson

  6. #6
    Registered User
    Join Date
    05-02-2016
    Location
    USA
    MS-Off Ver
    2013
    Posts
    35

    Re: Run-time error '1004'

    Quote Originally Posted by protonLeah View Post
    Your Integers should be reDim'd as Long
    You're absolutely correct that having those Dim'd as String was the cause of the issue. In my original post I said "I know (KNOW!) that I am missing something simple" and I was really right LOL - thanks for finding it!

    But with a report that is very definitely limited in size, is there a reason to use Long instead of Integer?
    Last edited by dhunton; 12-30-2016 at 01:02 AM.

  7. #7
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,570

    Re: Run-time error '1004'

    No, not any more since VBA automatically converts Integers to Longs. In the past, using Integer types for row variables could cause overflow errors so I just started always using Long vs. Integer. Max value for Integer is 32,768 and for Long it's 2,147,483,648

  8. #8
    Registered User
    Join Date
    05-02-2016
    Location
    USA
    MS-Off Ver
    2013
    Posts
    35

    Re: Run-time error '1004'

    Quote Originally Posted by protonLeah View Post
    No, not any more since VBA automatically converts Integers to Longs. In the past, using Integer types for row variables could cause overflow errors so I just started always using Long vs. Integer. Max value for Integer is 32,768 and for Long it's 2,147,483,648
    Thank you for the information!

+ 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. Replies: 1
    Last Post: 08-08-2016, 04:35 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. Addon error : Run-time error '1004': Method 'MacroOptions' of object '_Application' failed
    By jtcoleman in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 3
    Last Post: 02-05-2014, 12:23 PM
  4. Replies: 4
    Last Post: 11-15-2013, 05:03 PM
  5. VBA Code...error = run time error 1004 autofilter method of range class failed
    By Dariusd7 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 11-15-2013, 04:49 PM
  6. Error "run-time Error '1004': General Odbc Error
    By D4WNO77 in forum Access Tables & Databases
    Replies: 2
    Last Post: 07-16-2012, 09:55 AM
  7. [SOLVED] run-time error '1004': Application-defined or object-deifined error
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-10-2005, 05:05 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