CODEWITHSUNDEEP
excelvbaruntime-error
Sufarm.kr수련농원
Sufarm.kr수련농원

Reputation: 1

The issue about run-time error 1004 on vba

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.

Upvotes: 0

Views: 441

Answers (1)

Variatus
Variatus

Reputation: 14383

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.

Upvotes: 2

Related Questions