1004 Run-Time Error
If the code is behind a worksheet (not in a General module), then this kind of
expression:
Worksheets(1).Range(Cells(1, 1), Cells(1, 1).end(xldown))
is equivalent to:
Worksheets(1).Range(sheetwithcode.Cells(1, 1), sheetwithcode.Cells(1, 1))...
And if worksheets(1) isn't the same as the sheet with the code, you'll get an
error.
Dim row_count1 As Long
Dim row_count2 As Long
Dim split_text() As String
Dim x As Long
Dim No_Of_Elements As Long
'there's no real reason to activate the sheet first
with worksheets(1)
row_count1 = .Range(.Cells(1, 1), .Cells(1, 1).End(xlDown)).Cells.Count
end with
with Worksheets(2)
row_count2 = .Range(.Cells(3, 1), .Cells(3, 1).End(xlDown)).Cells.Count
end with
Those leading dots mean that those objects/properties belong to the object in
the previous With statement. In this case Worksheets(1) and worksheets(2).
I changed your Integer's to Long's and .rows.count to .cells.count. It
shouldn't matter in this case, but it could change the count depending on what
your range is (multiple areas, overlapping rows, ...).
Satish wrote:
Hi,
I have been at my wits end with this strange problem. I keep getting
a 1004 run-time error for the following piece of code although I dont
see anything wrong in the way I've written it.
I need to get the number of filled rows in both sheets 1 and 2. I am
running this macro on a command button in Sheet2. And the error
occurs at the line row_count1 = Worksheets(1).Range(Cells(1, 1), Cells
(1, 1).End(xlDown)).Rows.Count
If I skip that line, the row_count2 line executes perfectly fine. Both
the statements are very similar, yet I get the error. :(
Can someone please suggest some solutions?
Dim row_count1 As Integer
Dim row_count2 As Integer
Dim split_text() As String
Dim x As Integer
Dim No_Of_Elements As Integer
Worksheets(1).Activate
row_count1 = Worksheets(1).Range(Cells(1, 1), Cells(1, 1).End
(xlDown)).Rows.Count
Worksheets(2).Activate
row_count2 = Worksheets(2).Range(Cells(3, 1), Cells(3, 1).End
(xlDown)).Rows.Count
Thanks,
Satish
--
Dave Peterson
|