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