run time error 1004

[Guest]

when i run the code given below, sometimes i get "error 1004, select method
of range class failed"
error comes in line Sheet8.Range("M5").Select
interesting thing is i get this error only some of the time.
when i am running this module independently, no error comes.
when i call this module from some other module then the error comes.
this code resides in module and the code from which i call for this module
is also written in module (none are in sheet)

please help!!

regards
pradip


Public Sub RevertMod()
'
' Revert Macro
' Macro recorded 4/20/2005 by 112968
'

'
Dim Days As Integer
Days = Sheet8.Range("B1")
Sheet8.Range("M5").Select
ActiveCell.Offset(Days, 0).Range("A1").Select
ActiveCell.GoalSeek Goal:=Sheet8.Range("j1"), ChangingCell:=Sheet8.Range("J2")
Sheet8.Range("A1").Select
End Sub

 

[Guest]

sometimes i also get application-defined or object-defined error on line
Sheet8.Range("M5").Select

 

[Dave Peterson]

You can only select a cell on a sheet that's selected.

so maybe adding:
sheet8.select
at the top of your routine would fix the problem

(and when you ran the code by hand, maybe that sheet8 was active--just by
coincidence.

 

[Darrin Henshaw]

By chance when you get the run time 1004 error, is the active sheet
Sheet8. Using the Immediate window, if the active sheet is not Sheet8 I
get the same error, but if the activesheet is Sheet8 it works perfectly.
I would add a line to like Sheet8.Activate. See if that helps, or you
could add some error trapping, say On Error GoTo ErrorHandler, then in
the ErrorHandler, have the Sheet8.Activate.