[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.
Re: [Excel VBA] Run-time error '1004'
Quote:
Originally Posted by
ActSciMan
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?
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
