Getting Run-time error although code looks correct

[Guest]

Hi,

I have been running into an issue for years since I first started coding in
VBA within Excel -- it has driven me crazy and significantly effects my
ability to write code without having to record a macro...the issue appears to
be interment and just seems to not make logical sense.

The issue is that for some reason, when I write, for example, the code below
sometimes it does not work and I get the error listed below. When, however,
I copy the same line of code from another sub, where that same code works,
THEN the error stops coming up. It is literally that the code is identical -
it is just that when I copy it from another sub where the code has worked
INSTEAD of typing the code out, then the code works. Any insight would be
GREATLY appreciated as this is killing me -- it is like water torture -- it
has gotten to the point where I can't take it any more I want to
understand what the issue is and how I can avoid it.

Code:
Sheets("Payment Sales Master").Range(Rows(2),
Rows(2).End(xlDown)).ClearContents

Error:
Run-time error '1004':

Application-defined or object-defined error

 

[Gary Keramidas]

works here, sure the sheet is not protected?

With Sheets("Payment Sales Master")
.Unprotect
.Range(Rows(2), Rows(2).End(xlDown)).ClearContents
.Protect
End With
End Sub

 

[Gary Keramidas]

or maybe this if it's not the active sheet

Sub test()
Dim ws As Worksheet
Set ws = Worksheets("Payment Sales Master")
With ws
.Unprotect
.Range(ws.Rows(2), ws.Rows(2).End(xlDown)).ClearContents
.Protect
End With
End Sub

 

[NickHK]

To help solve these kind of problems of referencing cells on the correct WS,
you need to understand how Excel determines the Range you mean when
unqualifies ranges are used.
Maybe the code the code below will help:

'<WS1 Code>
Private Sub CommandButton1_Click()

Worksheets(1).Activate
Call SheetTest_Module

Worksheets(2).Activate
Call SheetTest_Module

Call SheetTest_WS

Call Sheet2.SheetTest_WS

End Sub

Public Sub SheetTest_WS()
Debug.Print Range("A1").Parent.Name
'Name that contains this code: = Me.Name
End Sub
'<WS1 Code>

'<WS2 Code>
Public Sub SheetTest_WS()
Debug.Print Range("A1").Parent.Name
'Name that contains this code: = Me.Name
End Sub
'<WS2 Code>

'<Module Code>
Public Sub SheetTest_Module()
Debug.Print Range("A1").Parent.Name
'Name of ActiceSheet
End Sub
'</Module Code>

The most robust way to avoid these error is to fully qualify the
ranges/cells that you are referring to.
Notice the addition of the ".", so the Rows are no longer unqualified:

With Sheets("Payment Sales Master")
.Range(.Rows(2), .Rows(2).End(xlDown)).ClearContents
End With

Of course, if you really need the code to refer to whatever is the
ActiveSheet or the sheet that the code resides on, then use the unqualified
method in the appropriate location.

NickHK

 

[NickHK]

Forgot to add:
So yes, with your posted code you will get internittent error depending on
where this code resides.

Sheets("Payment Sales Master").Range(Rows(2),
Rows(2).End(xlDown)).ClearContents

- If in the "Payment Sales Master" WS, OK, as Rows refers to the same sheet
as Range.
- If in a module AND "Payment Sales Master" is the ActiveSheet, OK, as Rows
refers to the same sheet as Range.
- If in a module AND "Payment Sales Master" is NOT the ActiveSheet, ERROR,
as Rows refers to the ActiveSheet, whilst as .Range refers to
Sheets("Payment Sales Master").

NickHK

 

[Guest]

Thanks Nick. I think you are right in that I was not understanding how Excel
determines the Range. Based on your explanation below as well as your
subsequent post on this issue, it sounds like the Range I was setting was
unqualified, which I assume means that it is not tied to one sheet - it is
tied to whichever sheet is active - is that right? Again, from your
explanation, it sounds like the way I can aovid this is by using the With
loop with "." which I assume then ties the Range to the sheet bein referenced
in the With statement. Is this correct?

Thanks so much for your input.

 

[Guest]

Thanks Gary. Based on your post and Nick's post, I'm going to use the With
statement as I now understand that this ties the range being defined to the
sheet in the With statement.

Thanks!