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

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

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

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

On Jul 10, 7:54*pm, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> 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 onwhat
> 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- Hide quoted text -
>
> - Show quoted text -

Thanks a ton! Your solution worked like a charm.

regards,
Satish