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
    I'm about to be a PowerPoster! Hack's Avatar
    Join Date
    Aug 2001
    Location
    Searching for mendhak
    Posts
    58,333

    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?

  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 02: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
  •  



Click Here to Expand Forum to Full Width