Range type variable oddness

[Nick]

I wish to do this:

Dim TempData As Worksheet
Dim SearchRange As Range

Set TempData = ThisWorkbook.Worksheets("Temp Data")

Set SearchRange = TempData.Range(Cells(1, 1), Cells(1, 26))

The code currently comes up with an error message saying
the range method has failed.

I do need to declare tempdata and searchrange separately
cos I will be changing the searchrange in a "for, next"
loop.

I always thought that range was a property of worksheets
not a method. Bit confused and although I have tried many
different variations I can't get one to work.

Any reponse would be, as always, much appreciated.

Nick Shinkins

 

[Chip Pearson]

Nick,

Your Cells properties are not pointing to the TempData worksheet.
They refer to cells on the currently active worksheet. Try


With TempData
Set SearchRange = .Range(.Cells(1,1),.Cell(1,26))
End With

Note the three leading periods.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Tom Ogilvy]

you unqualified Cells,Cells refers to the activesheet rather than Tempdata
(or if the code is in a sheet module, they refer to the sheet containing the
code).

With TempData
Set SearchRange = .Range(.Cells(1, 1), .Cells(1, 26))
End With

 

[Bob Phillips]

Nick,

This works fine for me. What error do you get?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Alan Beban]

Here's a way to avoid the lack of qualification problem referred to by
Tom Ogilvy and Chip Pearson:

Dim TempData As Worksheet
Dim rng As Range, SearchRange As Range
Set TempData = Sheets("Sheet3")
Set rng = TempData.Range("A1")
Set SearchRange = TempData.Range(rng(1, 1), rng(1, 26))

Alan Beban

 

[Alan Beban]

Nick,

This works fine for me. What error do you get?

From the OP's original post:

"The code currently comes up with an error message saying
the range method has failed."

As Chip Pearson and Tom Ogilvy already pointed out, the originally
posted code works only if TempData is the active sheet.

Alan Beban

 

[Bob Phillips]

Not if that sheet was selected, which is what was the case when I tested and
replied.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Nick]

Thanks for all your replies.

Just got back to work to a complete solution

Should have known really since I have used the leading
periods on many past occasions.

The code works fine now.

Nick