Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
1004 Run-Time Error
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
1004 Run-Time Error
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
1004 Run-Time Error
On Jul 10, 7:54*pm, Dave Peterson 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 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- Hide quoted text - - Show quoted text - Thanks a ton! Your solution worked like a charm. :) regards, Satish |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
run time error 1004 general odbc error excel 2003 vba | Excel Programming | |||
Run Time Error 1004: Application or Object Defined Error | Excel Programming | |||
Run Time 1004 Error: Application or Object Difine Error | Excel Programming | |||
run-time error '1004': Application-defined or object-deifined error | Excel Programming | |||
Run time error '1004': Generaol ODBC error | Excel Programming |