Error on Set Rng statement?
 

Hi all. Any idea why this line errors out if the sheet Data is not
the active sheet? If I run the code while on the Data sheet, all
works well. If I run thwe code while on any other sheet, I get an
"application defined or object defined error". Thanks!

Set rng = Worksheets("Data").Range(Cells(1, 1), Cells(Rows.Count,
1).End(xlUp))

Error on Set Rng statement?
 

Sub routine()
Set rng = Worksheets("Data").Range(Cells(1, 1), Cells(Rows.Count,
1).End(xlUp))
MsgBox (rng.Address)
End Sub

will work if:

1. there is a worksheet named Data
2. column A in that sheet has some data in it.
--
Gary''s Student - gsnu2007e


"Steve" wrote:

Hi all. Any idea why this line errors out if the sheet Data is not
the active sheet? If I run the code while on the Data sheet, all
works well. If I run thwe code while on any other sheet, I get an
"application defined or object defined error". Thanks!

Set rng = Worksheets("Data").Range(Cells(1, 1), Cells(Rows.Count,
1).End(xlUp))

Error on Set Rng statement?
 

When you have an unqualified range in a general module, it will refer to the
activesheet.

When you have an unqualified range in a worksheet module, it will refer to the
sheet holding the code.

so this will work

Set rng = Worksheets("Data").Range(Worksheets("Data").Cells( 1, 1), _
Worksheets("Data").Cells(Rows.Count, 1).End(xlUp))

as will:

with Worksheets("Data")
Set rng = .Range(.Cells(1, 1), .Cells(Rows.Count, 1).End(xlUp))
End With

I'd use:

with Worksheets("Data")
Set rng = .Range("A1", .Cells(.Rows.Count, 1).End(xlUp))
End With


Steve wrote:

Hi all. Any idea why this line errors out if the sheet Data is not
the active sheet? If I run the code while on the Data sheet, all
works well. If I run thwe code while on any other sheet, I get an
"application defined or object defined error". Thanks!

Set rng = Worksheets("Data").Range(Cells(1, 1), Cells(Rows.Count,
1).End(xlUp))

--

Dave Peterson

Error on Set Rng statement?
 

Awesome. Thanks guys!!

On Mar 4, 4:28*pm, Dave Peterson wrote:
When you have an unqualified range in a general module, it will refer to the
activesheet.

When you have an unqualified range in a worksheet module, it will refer to the
sheet holding the code.

so this will work

Set rng = Worksheets("Data").Range(Worksheets("Data").Cells( 1, 1), _
* * * * * * * Worksheets("Data").Cells(Rows.Count, 1).End(xlUp))

as will:

with Worksheets("Data")
* Set rng = .Range(.Cells(1, 1), .Cells(Rows.Count, 1).End(xlUp))
End With

I'd use:

with Worksheets("Data")
* Set rng = .Range("A1", .Cells(.Rows.Count, 1).End(xlUp))
End With

Steve wrote:

Hi all. *Any idea why this line errors out if the sheet Data is not
the active sheet? *If I run the code while on the Data sheet, all
works well. *If I run thwe code while on any other sheet, I get an
"application defined or object defined error". *Thanks!

Set rng = Worksheets("Data").Range(Cells(1, 1), Cells(Rows.Count,
1).End(xlUp))

--

Dave Peterson