|
Run-Time error 1004 issue just not making sense
I am getting the "Run Time error '1004': Application-defined or object
defined error" error for the following line of code when executed. --------------------- Sheets("Open Transactions").Range(Rows(2), Rows(2).End (xlDown)).ClearContents --------------------- What drives me crazy is that I have similar lines of code that when executed don't return an error. Examples of those lines of code are below. I'm trying to understand why the line above has the error come up when those below do not? -------------------------- If Aamount < Bamount Then Sheets("Open Transactions").Range("D1").Offset(x, 0).value = A Sheets("Open Transactions").Range("D1").Offset(x, -3).value = Worksheets("Member ID Report Master Pivot").Range(A.Address()).Offset(0, 1).value Sheets("Open Transactions").Range("D1").Offset(x, -2).value = Worksheets("Member ID Report Master Pivot").Range(A.Address()).Offset(0, 2).value -------------------------- One note -- I have found a workaround for the error which is the following code -- what doesn't make sense is that I don't see why I need to activate the sheet for this code to work, especially considering that similar code seems to work in other parts of the module where this code is being written (that code shown directly above). -------------------------- Sheets("Open Transactions").Activate Range(Rows(2), Rows(2).End(xlDown)).ClearContents -------------------------- -- Robert |
Run-Time error 1004 issue just not making sense
Robert,
Rows(2) without a qualifier refers to the active sheet. Is the active sheet Sheets("Open Transactions") ? Unless there is only one sheet , you should always qualify any references to worksheets ... With Sheets("Open Transactions") .Range(.Rows(2), .Rows(2).End(xlDown)).ClearContents End With (note the dots) -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware (Excel add-ins... sort colors, compare, arrange columns, thesaurus "robs3131" wrote in message I am getting the "Run Time error '1004': Application-defined or object defined error" error for the following line of code when executed. --------------------- Sheets("Open Transactions").Range(Rows(2), Rows(2).End (xlDown)).ClearContents --------------------- What drives me crazy is that I have similar lines of code that when executed don't return an error. Examples of those lines of code are below. I'm trying to understand why the line above has the error come up when those below do not? -------------------------- If Aamount < Bamount Then Sheets("Open Transactions").Range("D1").Offset(x, 0).value = A Sheets("Open Transactions").Range("D1").Offset(x, -3).value = Worksheets("Member ID Report Master Pivot").Range(A.Address()).Offset(0, 1).value Sheets("Open Transactions").Range("D1").Offset(x, -2).value = Worksheets("Member ID Report Master Pivot").Range(A.Address()).Offset(0, 2).value -------------------------- One note -- I have found a workaround for the error which is the following code -- what doesn't make sense is that I don't see why I need to activate the sheet for this code to work, especially considering that similar code seems to work in other parts of the module where this code is being written (that code shown directly above). -------------------------- Sheets("Open Transactions").Activate Range(Rows(2), Rows(2).End(xlDown)).ClearContents -- Robert |
Run-Time error 1004 issue just not making sense
One thing you need to do is fully qualify all of your range references:
Sheets("Open Transactions").Range(Sheets("Open Transactions").Rows(2), Sheets("Open Transactions").Rows(2).End(xlDown)).ClearContents Shortened to: With Sheets("Open Transactions") .Range(.Rows(2), .Rows(2).End(xlDown)).ClearContents End With "robs3131" wrote: I am getting the "Run Time error '1004': Application-defined or object defined error" error for the following line of code when executed. --------------------- Sheets("Open Transactions").Range(Rows(2), Rows(2).End (xlDown)).ClearContents --------------------- What drives me crazy is that I have similar lines of code that when executed don't return an error. Examples of those lines of code are below. I'm trying to understand why the line above has the error come up when those below do not? -------------------------- If Aamount < Bamount Then Sheets("Open Transactions").Range("D1").Offset(x, 0).value = A Sheets("Open Transactions").Range("D1").Offset(x, -3).value = Worksheets("Member ID Report Master Pivot").Range(A.Address()).Offset(0, 1).value Sheets("Open Transactions").Range("D1").Offset(x, -2).value = Worksheets("Member ID Report Master Pivot").Range(A.Address()).Offset(0, 2).value -------------------------- One note -- I have found a workaround for the error which is the following code -- what doesn't make sense is that I don't see why I need to activate the sheet for this code to work, especially considering that similar code seems to work in other parts of the module where this code is being written (that code shown directly above). -------------------------- Sheets("Open Transactions").Activate Range(Rows(2), Rows(2).End(xlDown)).ClearContents -------------------------- -- Robert |
Run-Time error 1004 issue just not making sense
Thanks Jim and JMB!
-- Robert "JMB" wrote: One thing you need to do is fully qualify all of your range references: Sheets("Open Transactions").Range(Sheets("Open Transactions").Rows(2), Sheets("Open Transactions").Rows(2).End(xlDown)).ClearContents Shortened to: With Sheets("Open Transactions") .Range(.Rows(2), .Rows(2).End(xlDown)).ClearContents End With "robs3131" wrote: I am getting the "Run Time error '1004': Application-defined or object defined error" error for the following line of code when executed. --------------------- Sheets("Open Transactions").Range(Rows(2), Rows(2).End (xlDown)).ClearContents --------------------- What drives me crazy is that I have similar lines of code that when executed don't return an error. Examples of those lines of code are below. I'm trying to understand why the line above has the error come up when those below do not? -------------------------- If Aamount < Bamount Then Sheets("Open Transactions").Range("D1").Offset(x, 0).value = A Sheets("Open Transactions").Range("D1").Offset(x, -3).value = Worksheets("Member ID Report Master Pivot").Range(A.Address()).Offset(0, 1).value Sheets("Open Transactions").Range("D1").Offset(x, -2).value = Worksheets("Member ID Report Master Pivot").Range(A.Address()).Offset(0, 2).value -------------------------- One note -- I have found a workaround for the error which is the following code -- what doesn't make sense is that I don't see why I need to activate the sheet for this code to work, especially considering that similar code seems to work in other parts of the module where this code is being written (that code shown directly above). -------------------------- Sheets("Open Transactions").Activate Range(Rows(2), Rows(2).End(xlDown)).ClearContents -------------------------- -- Robert |
| All times are GMT +1. The time now is 01:58 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com