herzberg
03-07-2008, 12:56 AM
Hi all,
Take a look at the code below:
Public Sub TestMe()
Dim TargetSheet As Worksheet, Activerg As Range
Set TargetSheet = Sheet2
With TargetSheet
Set Activerg = .Range(Cells(1, 1), Cells(1, 1))
MsgBox Activerg.Address
End With
End Sub It works perfectly OK when it's in a module, ThisWorkBook or Sheet2 itself but returns a 1004 runtime error when the exact same code is placed within any other sheets. The error occurs at the Set statement.
I only noticed this error today, as I took a detour from my usual habit of lumping all the code into modules. So like, can anyone enlighten me on the cause(s) of this error? I guess it has something to do with the object scope or something of the like but I think you guys can provide a better and more detailed explanation to this.
Take a look at the code below:
Public Sub TestMe()
Dim TargetSheet As Worksheet, Activerg As Range
Set TargetSheet = Sheet2
With TargetSheet
Set Activerg = .Range(Cells(1, 1), Cells(1, 1))
MsgBox Activerg.Address
End With
End Sub It works perfectly OK when it's in a module, ThisWorkBook or Sheet2 itself but returns a 1004 runtime error when the exact same code is placed within any other sheets. The error occurs at the Set statement.
I only noticed this error today, as I took a detour from my usual habit of lumping all the code into modules. So like, can anyone enlighten me on the cause(s) of this error? I guess it has something to do with the object scope or something of the like but I think you guys can provide a better and more detailed explanation to this.