Anyone know why this doesn't work
 

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

Anyone know why this doesn't work
 

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.



In article ,
"BT" wrote:

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!

Anyone know why this doesn't work
 

Dear Jem,

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


"JE McGimpsey" wrote in message
...
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.



In article ,
"BT" wrote:

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!