Anyone know why this doesn't work

[BT]

I am sure it is obvious but when I have been programming intensely
apparently obvious things become obscured, any help appreciated:

Dim InvVCVRange As Range
Dim AlphaRange As Range

Set InvVCVRange = Sheets("InvVCV").Range(Cells(2, 2), Cells(11, 11))


I get application definmed or object defined error!

Kind regards, Mark

 

[JE McGimpsey]

Cells defaults to the ActiveSheet if not fully qualified. So

Set InvVCVRange = Sheets("InvVCV").Range(Cells(2, 2), Cells(11, 11))

is equivalent to

Set InvVCVRange = Sheets("InvVCV").Range(ActiveSheet.Cells(2, 2), _
ActiveSheet.Cells(11, 11))

so if InvVCV is not active, you'll get an error, since ranges can only
exist on a single sheet. Try

With Sheets("InvVCV")
Set InvVCVRange = .Range(.Cells(2, 2), .Cells(11, 11))
End With

instead.

 

[BT]

Dear Jem,

I really appreciate the help, works perfectly now - I love this user group