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.

 

[Dukesford]

Yes!!!

Dave,

thank you soooo much i have been trying to figure this out for days. However, do you know of away for that to work without going to that worksheet? Sometimes I will have multiple worksheets and I do not want to go to all of them and then back.

So this is my code shortened a lot:

Sub SF1_Calculation()
With Sheets("sf1").Activate
Dim lastrow As Long
lastrow = Cells(Rows.Count, 2).End(xlUp).Row
With Range("i3").Select
Range("I3").Formula = "=B3"
Selection.AutoFill Destination:=Range("i3:i" & lastrow)
End With
End With
End Sub


Is that possible without switching pages????