-2

I know this is a stupid question, but I'm at wit's end trying to figure it out.

This little snippet of code is causing a Run-time error '1004': Method 'Range' of object '_Worksheet' failed. But only when Sheet3 is not the active sheet. It does what's expected when Sheet3 is active.

With Sheet3.Range(Cells(1, 1), Cells(nrows1, ncols1))
    .Clear
    .Value = SCDevNeeds1
End With

Furthermore, if I change the reference to the sheet's tab name, I still get a run-time error, but this time it's an Application-defined or Object-defined error. This ALSO happens only when the sheet is not active:

With Sheets("newest").Range(Cells(1, 1), Cells(nrows1, ncols1))
    .Clear
    .Value = SCDevNeeds1
End With

What am I missing?

ROMANIA_engineer
  • 54,432
  • 29
  • 203
  • 199
SteveS
  • 33
  • 1
  • 8
  • 1
    What are the values of **nrows1, ncols1,** and **SCDevNeeds1** when the lines are executed?? – Gary's Student Oct 18 '14 at 21:41
  • 4
    Cells() always refers to the active sheet if you don't qualify if with a reference to a specific sheet. Your code errors because the Range refers to sheet3 but the Cells() do not unless sheet3 is active. – Tim Williams Oct 18 '14 at 21:43
  • 1
    In this case yes, but not always, depends on where the code is, which makes it even more error-prone. (see my answer below) – KekuSemau Oct 18 '14 at 22:02
  • I don't think this question deserves a downvote, if you're not into it, you don't know and see it. Gary's Student's comment proves that even others don't see it immediately. – KekuSemau Oct 18 '14 at 22:10
  • 1
    @KekuSemau: Seriously? I can show you at least 5 questions in Stackoverflow which address this problem including one link in which Tim Williams (One of the posters from above) answered few weeks ago. The downvote is for lack of research. – Siddharth Rout Oct 18 '14 at 22:13
  • 1
    thanks pnuts. i didn't pick a fight; i believe the almighty Siddarth did. i don't care how talented a programmer he is, and based on my (limited) experience, he certainly isn't tolerant. at least one other person felt that my question didn't merit a downvote. at the end of the day, i doubt it will have any measurable effect on my life, but it was unnecessary, and chastening me like an errant schoolboy was arrogant at best. "anonymity" means that I doubt he would have been so aggressive if he were standing physically in front of me. ok, anonymity is the wrong word to have used. – SteveS Oct 18 '14 at 23:10
  • 2
    I think you seriously need to visit the help section of SE. `Downvoting` is no sign of being intolerant. If you hover your mouse on the downvote, you'll see what the tooltip says. In my first comment neither was I rude or offensive towards you. So I am not sure which comment of mine made you think that I am picking a fight. Nor have I portrayed here or in SE ever that I am the one who knows it all :). Unfortunately, you have been behaving (since your first comment) in the most inappropriate manner. Anyways, I will make sure that this is my last comment as I don't see this going anywhere... :) – Siddharth Rout Oct 18 '14 at 23:24

2 Answers2

1

Cells in the expression Sheet3.Range(Cells(1, 1), Cells(nrows1, ncols1)) refers to:
a) the active sheet if the code is a seperate code module
b) the module's sheet if the code is in a worksheet-module.

So the code would work if it's inside the Sheet3-module, but rather by accident.
You should always use full references to all Range objects (like Cells, Range, Rows, Columns, ...).

Sheet3.Range(Sheet3.Cells(1, 1), Sheet3.Cells(nrows1, ncols1))

KekuSemau
  • 6,830
  • 4
  • 24
  • 34
1

Try this code snippet

With ThisWorkbook.Sheets("Sheet3")
    With .Range(.Cells(1, 1), .Cells(100, 2))
        .Clear
        .Value = ""
    End With

End With

ZAZ
  • 597
  • 3
  • 6
  • Thanks ZAZ. KekuSemau's worked. Your suggestion seems to be very efficient code, but it gave me a subscript out of range error, which I don't feel like figuring out! I do appreciate the answer, though. I know I could get it working. – SteveS Oct 18 '14 at 22:11
  • Relatively new to this forum. Can I accept both answers? Seems like it won't let me. I did upvote your answer. – SteveS Oct 18 '14 at 22:39