Application-defined or object-defined error - missing the basics

[brentfid]

I am confident that the following problem stems from my lack of
understanding the core basics of VB.

The following code works even when the sheet "CostAttributes" is not
selected.

Dim cellcount As Long
Sheets("CostAttributes").Select
cellcount = Sheets("CostAttributes").Range(("A7"),
Range("A7").End(xlDown)).Rows.Count

However I am trying to avoid having to select the "CostAttributes"
sheet in the code so I have written:

Dim cellcount As Long
cellcount = Sheets("CostAttributes").Range(("A7"),
Range("A7").End(xlDown)).Rows.Count

and I get the application-defined or object-defined error message.
Can anyone please help a lost newbie with this?

TIA,

Brent

 

[Bernie Deitrick]

Brent,

You need to fully qualify your range object, otherwise, any range object
defaults to the active sheet. Simply change

cellcount = Sheets("CostAttributes").Range(("A7"), _
Range("A7").End(xlDown)).Rows.Count

to

With Sheets("CostAttributes")
cellcount = .Range(.Range("A7"), .Range("A7").End(xlDown)).Rows.Count
End With

which is the same as:

cellcount =
Sheets("CostAttributes").Range(Sheets("CostAttributes").Range("A7"),
Sheets("CostAttributes").Range("A7").End(xlDown)).Rows.Count

HTH,
Bernie
MS Excel MVP