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

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


"Nick" wrote in message
...
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

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

--
Regards,
Tom Ogilvy

"Nick" wrote in message
...
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

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)

"Nick" wrote in message
...
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

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

Nick wrote:
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

Bob Phillips wrote:

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

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)

"Alan Beban" wrote in message
...
Bob Phillips wrote:

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

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