Solved: Range selection with array of ranges

[pepe90]

Hi. Again I'm having problems with the selection of a range. In this case I have an array of ranges pos_canaletas created as follows:


Dim pos_canaletas(1 To 34) As Variant
ThisWorkbook.Sheets("SIMULACIÓN").Activate
For i = 1 To 34
If i <= 8 Then
pos_canaletas(i) = ActiveSheet.Range(Cells(i1, 4), Cells(i1, 8))
i1 = i1 + 2
ElseIf i <= 11 Then
pos_canaletas(i) = ActiveSheet.Range(Cells(i2, 4), Cells(i2, 8))
i2 = i2 + 2
ElseIf i <= 14 Then
pos_canaletas(i) = ActiveSheet.Range(Cells(i3, 13), Cells(i3, 18))
i3 = i3 - 2
ElseIf i <= 22 Then
pos_canaletas(i) = ActiveSheet.Range(Cells(i4, 13), Cells(i4, 18))
i4 = i4 - 2
ElseIf i <= 28 Then
pos_canaletas(i) = ActiveSheet.Range(Cells(4, j1), Cells(9, j1))
j1 = j1 + 2
Else
pos_canaletas(i) = ActiveSheet.Range(Cells(13, j2), Cells(18, j2))
j2 = j2 + 2
End If
Next

And before i have the following loop:

For J = 1 To X
ActiveWorkbook.Sheets("SIMULACIÓN").Activate
ActiveSheet.pos_canaletas(J).Select
With Selection.Interior
.ColorIndex = 42
.Pattern = xlSolid
End With
Sheets("BD").Activate
Next J

And I get an error on the second line of the loop (The selection of the range pos_canaletas(J)) and it says something like "The object doesn't allow this property or method".

[CatDaddy]

Cells(i3, 1) --> Cells(i*3,1)

but im a little confused as to what you are trying to accomplish here a select case might clean up your ifelseifelseif too btw

[pepe90]

Sorry I didn't explain that. i1,i2,i3 are just counter names, but I think that part is fine, I mentioned it just to explain that pos_canaletas is an array of ranges.

[Kenneth Hobs]

You can use Range for your array but when you set the elements, use the Set command as usual for Range objects.

Try to get away from using Activate or Select if you don't need to.

e.g.
Dim pos_canaletas(1 To 34) As Range
With ThisWorkbook.Sheets("SIMULACIÓN")
For i = 1 To 34
If i <= 8 Then
Set pos_canaletas(i) = .Range(Cells(i1, 4), Cells(i1, 8))
i1 = i1 + 2
'..snip
End If
Next
End With

[pepe90]

The section where I define the array seems to be working just fine. Using or not using set I have the same problem on the second part. I don't understand why on the line "ActiveSheet.pos_canaletas(J).Select" it says that the object doesn't allow that method.

[Aflatoon]

You must use Set as Kenneth said since you are dealing with object variables. You also need to alter the second piece of code to just use:

pos_canaletas(J).Select
since your array is not a property of the sheet. Better still, as Kenneth also said, don't select:


For J = 1 To X
With pos_canaletas(J).Interior
.ColorIndex = 42
.Pattern = xlSolid
End With
Next J

[pepe90]

OK, understood. But the problem I have is that on each iteration of the For loop it has to put some values on the "BD" worksheet (I avoid puting this lines on the post) and then change some cell colors on the "SIMULACIÓN" worksheet, but despite this latter worksheet is activated, this changes are made on the "BD" worksheet.

Maybe is better to do the work of changing the cell's colors on an extra For?

[Aflatoon]

If the code I posted colours the wrong sheet, then the ranges were added incorrectly. The code that sets up the array should look like:


Dim pos_canaletas(1 To 34) As Variant
With ThisWorkbook.Sheets("SIMULACIÓN")
For i = 1 To 34
If i <= 8 Then
Set pos_canaletas(i) = .Range(.Cells(i1, 4), .Cells(i1, 8))
i1 = i1 + 2
ElseIf i <= 11 Then
Set pos_canaletas(i) = .Range(.Cells(i2, 4), .Cells(i2, 8))
i2 = i2 + 2
ElseIf i <= 14 Then
Set pos_canaletas(i) = .Range(.Cells(i3, 13), .Cells(i3, 18))
i3 = i3 - 2
ElseIf i <= 22 Then
Set pos_canaletas(i) = .Range(.Cells(i4, 13), .Cells(i4, 18))
i4 = i4 - 2
ElseIf i <= 28 Then
Set pos_canaletas(i) = .Range(.Cells(4, j1), .Cells(9, j1))
j1 = j1 + 2
Else
Set pos_canaletas(i) = .Range(.Cells(13, j2), .Cells(18, j2))
j2 = j2 + 2
End If
Next
End With

[pepe90]

Perfect. It worked. Thank you all very much. Two last question and maybe I won't bother you too much.
What's the difference between this two lines?
Range(.Cells(a,b),.Cells(c,d))
Range(Cells(a,b),Cells(c,d)

And when I have to use "set" instruction?

[Aflatoon]

You have to use Set if your variable is an object type (like Range or Worksheet for example) but not if it's a data type, like String, Integer or Double.

When you use a With ... End With block, you use the dot in front of any properties or methods that belong to the object in the With statement. So:

With sheets("Sheet1")
.Range(.Cells(1, 1), .Cells(2, 1)).Value = "hello world"
End With
for example. Both the Range and Cells properties refer back to the Sheets("Sheet1") object. If you don't use the dot, then the With...End With has no effect.

[pepe90]

Thank you. Everything is clearer now