Set worksheet range runtime error 1004

[DaveP]

Hi People,

I am trying to reference a range on another worrksheet in the same
woorkbook, but when I try and set the range I get a run-time error 1004
Application-defined or object-defined error. I have had this problem
in the past and you can get around this error by selecting the
worksheet before the range, but shouldn't the code I have below work?

Function GetSP(NewRec As DB_SP, Location As String, RecDate As Date) As
Integer
'Get stockpile number from SP_Info based on date range and location
number

Dim myRange As Range
Dim myCell As Range

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Set myRange = Worksheets("SP Info").Range(Cells(2, 1),
Cells(Rows.Count, 1).End(xlUp))
For Each myCell In myRange
If (myCell.Offset(0, 1).Value = CInt(Location)) And
(myCell.Offset(0, 2).Value <= RecDate) Then
If myCell.Offset(0, 3).Value >= RecDate Or myCell.Offset(0,
3).Value = Empty Then
GetSP = myCell.Value
Exit For
End If
End If
Next myCell
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Function

TIA

Dave.

 

[DaveP]

Never mind people, it pays to look in the correct place.....

In answer to my own problem:

You need to activate the worksheet first, I added the following line
just before the set myRange.

Worksheets("SP Info").Activate

Regards

Dave.