1004 Run-Time Error

[Satish]

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 Nf_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

 

[Barb Reinhardt]

Try redomensioning Row_Count1 and 2 as Long and see if that helps.

 

[Dave Peterson]

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 Nf_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]

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 Nf_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 onwhat
your range is (multiple areas, overlapping rows, ...).












--

Dave Peterson- Hide quoted text -

- Show quoted text -

Thanks a ton! Your solution worked like a charm.

regards,
Satish