CODEWITHSUNDEEP
excelvba
fydelio
fydelio

Reputation: 963

Declaring a range

I'm close to going nuts! I'm somehow really struggling to get the range object right. If I'm entering this, I always get an error message: 

Dim ValidationRange As Range    
Set ValidationRange = Tabelle3.Range(Cells(1, 1), Cells(4, 1))

But if it enter the the range like this, everything is fine:

Dim ValidationRange As Range    
Set ValidationRange = Tabelle3.Range("a1:a4")

What am I doing wrong?

Upvotes: 0

Views: 124

Answers (3)

Brad
Brad

Reputation: 12253

What is happening when you declare your range like this 

Set ValidationRange = Tabelle3.Range(Cells(1, 1), Cells(4, 1))

Is really this

Set ValidationRange = Tabelle3.Range(ActiveSheet.Cells(1, 1), ActiveSheet.Cells(4, 1))

Because a cells object is just like a Range object except where a Range can by many cells, cells can only be one. Regardless, they are declared with respect to a parent object (sheet). If one is not specified ActiveSheet is assumed. If Tabelle3 is not your current active sheet then something is going to go wrong because a range in Tabelle3 cannot contain cells from another sheet. It doesn't make sense.

This will work because the parent of Cells is the same parent as the Range:

With Tabelle3
  .Range(.Cells(1, 1), .Cells(4, 1))
End With

Upvotes: 3

Kory
Kory

Reputation: 318

Try using your code without using Tabelle3. prior to your range statement. 

Dim ValidationRange As Range
Set ValidationRange = Range(Cells(1, 1), Cells(4, 1))

Worked for me.

Upvotes: 0

JFS
JFS

Reputation: 3152

you refer the range to the table but not the cells. try this:

With Tabelle3
  .Range(Cells(1, 1), Cells(4, 1))
End With

Upvotes: -1

Related Questions