Is it possible to store range objects in an variant array?

[stranno]

HI,

What is the difference between arr1 and arr2?
And why does the code" arr2(1,1).select throw an error?
I'm not very surprised by this, but why? After all, arr2(1,1) is a range object right?
And it is allowed to store range objects in a variant array.


Sub Test()
Dim arr1 As Variant, arr2 As Variant
Dim ws As Worksheet
Set ws = ThisWorkbook.ActiveSheet
arr1 = ws.Range("A1:C10").Value 'Values only
arr2 = ws.Range("A1:C10") 'The range object itself (at least i hoped so)
'This causes an error. I am not surprised
arr2(1, 1).Select
End Sub







Thank you so much for any comment.

Stranno

[p45cal]

arr2 = ws.Range("A1:C10") 'The range object itself (at least i hoped so)
Values again I'm afraid. Missing Set:
Set arr2 = ws.Range("A1:C10") 'The range object itself (for sure now)

The subsequent arr2(1, 1).Select should be fine.

Now this is storing a range in a variable, but your question is titled 'Is it possible to store range objects in an variant array? '
Yes, this stores 3 ranges in the variant array arr2:
arr2 = Array(ws.Range("A1:C10"), ws.Range("D1:E5"), ws.Range("F1:F3"))
then the equivalent of arr2(1, 1).Select will be:
arr2(0)(1, 1).Select

So no need for the Set command, the array contains range objects; to store values you'll have to be explicit:
arr2 = Array(ws.Range("A1:C10").Value, ws.Range("D1:E5").Value, ws.Range("F1:F3").Value)

[stranno]

Thanks p45cal,

You solved my problem.
How could I forget to use set?

Best regards,
Stranno

[jolivanes]

Or

Dim sh2Arr, sh4Arr, i As Long
sh2Arr = Array("A3:D9", "A13:D22", "A26:D35", "A39:D48")
sh4Arr = Array("A5:D11", "A16:D25", "A30:D39", "A44:D53")
For i = LBound(sh4Arr) To UBound(sh4Arr)
Sheets("ABCDEF").Range(sh4Arr(i)).Value = Sheets("GHIJKL").Range(sh2Arr(i)).Value
Next i

[snb]

@joli


Sub M_snb()
sn = Array([A3:D9], [A13:D22], [A26:D35], [A39:D48], [A5:D11], [A16:D25], [A30:D39], [A44:D53])

For j = 0 To 3
sn(j).Value = sn(j + 4).Value
Next
End Sub

[jolivanes]

@snb
Yes, indeed. Never thought about doing it that way.
Thanks