0

VBA Run-time error 1004

I'm programmng Excel Macro by VBA. Some problems happened.

There are 3 Excel files. One is old version excel file(extended format : xls) and the others are xlsx format.

I want to get the last row position of each file. My code is below.

Sub macro()

Dim testWorkBook1 As Workbook, testWorkBook2 As Workbook, testWorkBook3 As Workbook
Dim testSheet1 As Worksheet, testSheet2 As Worksheet, testSheet3 As Worksheet
Dim count1 As Integer, count2 As Integer, count3 As Integer

Set testWorkBook1 = Workbooks.Open("D:\test folder\test1.xls")      ' line a --> xls format
Set testWorkBook2 = Workbooks.Open("D:\test folder\test2.xlsx")
Set testWorkBook3 = Workbooks.Open("D:\test folder\test3.xlsx")      'line b

Set testSheet1 = testWorkBook1.Sheets(1)
Set testSheet2 = testWorkBook2.Sheets(1)
Set testSheet3 = testWorkBook3.Sheets(1)

count3 = testSheet3.Cells(Rows.count, 1).End(xlUp).Row
count2 = testSheet2.Cells(Rows.count, 1).End(xlUp).Row
count1 = testSheet1.Cells(Rows.count, 1).End(xlUp).Row    'the point where runtime error 1004 happened 


End Sub

When running code, run-time error 1004 happened. I tried serveral other cases. and i knew 2 things so far.

  1. if test1 file is "xlsx" version , the program runs successfully.
  2. if the 'line a' placed 'line b' below, the program also runs successfully. for example, 
Set testWorkBook2 = Workbooks.Open("D:\test folder\test2.xlsx")
Set testWorkBook3 = Workbooks.Open("D:\test folder\test3.xlsx")      'line b
Set testWorkBook1 = Workbooks.Open("D:\test folder\test1.xls")      ' line a --> xls format

I would like to get not only the solution but also the reasons.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Sufarm.kr수련농원
  • 1
  • 1
  • Try using `countX = testSheetX.Cells(testSheetX.Rows.count, 1).End(xlUp).Row`. Especially when working with different workbooks using `Rows`, `Cells` etc. can be tricky. – fourthquartermagic Mar 17 '20 at 08:11
  • One more note on finding the last row on a sheet. There are several ways to achieve this and not all deliver the same result. You may want to check which one fits best for you, see here for example: https://www.thespreadsheetguru.com/blog/2014/7/7/5-different-ways-to-find-the-last-row-or-last-column-using-vba – fourthquartermagic Mar 17 '20 at 08:14
  • 1
    You need to declare your row counting variables `As Long` instead of `Integer`. Excel has more rows than `Integer` can handle! I recommend to [always use Long instead of Integer](https://stackoverflow.com/questions/26409117/why-use-integer-instead-of-long/26409520#26409520) as there is no benefit in using `Integer` in VBA. Also make sure that you use `Worksheets` and not `Sheets`. Sheets can be a Chart sheet too and a chart sheet has no `.Rows`. So if your `Sheets(1)` is a chart it will fail. Use `Worksheets(1)` instead. – Pᴇʜ Mar 17 '20 at 09:48
  • Even though my poor english, i thanks a lot to everybody who wrote reply. – Sufarm.kr수련농원 Mar 18 '20 at 01:27

1 Answers1

2

Your syntax is faulty. This is how it should be.

with testSheet1
    count1 = .Cells(.Rows.Count, 1).End(xlUp).Row
End With

The critical difference is in the leading period here: .Rows.Count. This period makes VBA take the rows count from TestSheet1 whereas that count is taken from the ActiveSheet if omitted. Of course, in your example, the count on the ActiveSheet is much larger than in an XLS version worksheet and this causes the failure.

Variatus
  • 14,293
  • 2
  • 14
  • 30