Run-Time error 1004 issue just not making sense

[robs3131]

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).

 

[Jim Cone]

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" <[email protected]>
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).

 

[JMB]

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]

Thanks Jim and JMB!
--
Robert

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