PDA

View Full Version : Solved: Placement of code causes error



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.

mikerickson
03-07-2008, 01:31 AM
I think you want this.
With TargetSheet
Set Activerg = Range(.Cells(1, 1), .Cells(1, 1))
MsgBox Activerg.Address
End With
In this context, Range is not excatly a property, it acts more like a function of two range arguments.

Norie
03-07-2008, 07:11 AM
herzberg

What you are missing is the 2 little dots that mike's code has before Cells.

Without them VBA will assume you mean Cells on the active sheet.

herzberg
03-09-2008, 07:29 PM
herzberg

What you are missing is the 2 little dots that mike's code has before Cells.

Without them VBA will assume you mean Cells on the active sheet.
OH!! Now I see them. To think I spent 2 hours staring at the same line without noticing the missing periods. Oh well, I think I can be careless at times too. Thanks for pointing this "period" thingy out guys!