MsOf07 [Excel VBA] Run-time error '1004'-VBForums
Results 1 to 3 of 3

Thread: [Excel VBA] Run-time error '1004'

  1. #1

    Thread Starter
    Member
    Join Date
    May 2011
    Posts
    42

    [Excel VBA] Run-time error '1004'

    I am getting this error

    Run-time error '1004':

    Application-defined or object-defined error

    at this line of code:

    Code:
    NumVehTotal = Workbooks(DataFile).Worksheets("Data"). _
        Range("A2", Range("A" & EXCEL_MAX_ROWS + 2).End(xlUp)).Rows.Count
    The constant EXCEL_MAX_ROWS is 65000, and there are about 3500 rows of data, so I don't think there is any problem with getting a row number out of the correct range, like 65538. If I select some cell in column A, say A5000, and hit Ctrl-Up, it selects the last row of Data. So, I think the End(xlUp) should be working as I expect.

    In fact, when I run this program on one network drive, it works fine. When I run it on another network drive (I change the corresponding directories), I get this error. DataFile is the name of the workbook, including .xlsm, and I use that same string variable to open the file, so I don't see that causing any problems.

    I have no idea what's going on. Any help would be greatly appreciated.

  2. #2
    Super Moderator Hack's Avatar
    Join Date
    Aug 2001
    Location
    Searching for mendhak
    Posts
    58,335

    Re: [Excel VBA] Run-time error '1004'

    Quote Originally Posted by ActSciMan View Post
    In fact, when I run this program on one network drive, it works fine. When I run it on another network drive (I change the corresponding directories), I get this error.
    Same computer? Just different drive?

    Are the permissions exactly the same on both drives?
    Please use [Code]your code goes in here[/Code] tags when posting code.
    When you have received an answer to your question, please mark it as resolved using the Thread Tools menu.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    I dont answer coding questions via PM or EMail. Please post a thread in the appropriate forum section.

    Creating A Wizard In VB.NET
    Paging A Recordset
    What is wrong with using On Error Resume Next
    Good Article: Language Enhancements In Visual Basic 2010
    Upgrading VB6 Code To VB.NET
    Microsoft MVP 2005/2006/2007/2008/2009/2010/2011/2012/Defrocked

  3. #3

    Thread Starter
    Member
    Join Date
    May 2011
    Posts
    42

    Re: [Excel VBA] Run-time error '1004'

    Oh... so stupid. This is a program from someone else and I fixed it up good. I deleted every instance of Select and Activate and made sure every range was updated with the proper Workbooks().Worksheets()... BUT I missed the one above because it is inside a range statement that I did fix. And, the workbook I am working on was saved with a different worksheet activated. So, I think that's the entire problem. I selected the correct worksheet and saved it, and it appears to be working fine now. So, probably I should use

    Code:
    With Workbooks(DataFile).Worksheets("Data")
        NumVehTotal =  Range(.Range("A2"), .Range("A" & EXCEL_MAX_ROWS + 2).End(xlUp)).Rows.Count
    End With
    Also, when I was searching for a solution, I found that you had this same error 5 years ago, or at least the same error message.

    http://www.codeguru.com/forum/showthread.php?t=387162
    Last edited by ActSciMan; Jul 14th, 2011 at 03:40 PM.

Tags for this Thread

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.