Array as Range

[smpatty08]

I am trying to learn more about arrays and I am trying to set an array to a range of cells (B5:M12). I am getting an error in the following code.

Sub Macro3()Dim myArray() As Range, x, y As Integer


For x = 2 To 13
    For y = 5 To 12
        Set myArray(x, y) = Range(Cells(y, x))
    Next y
Next x
End Sub

Can somebody help me understand this? Thanks

 

[Fluff]

Easiest way is

Sub Macro3()
   Dim Ary As Variant
   Ary = Range("B5:M12").value2
End Sub

 

[smpatty08]

Easiest way is

Sub Macro3()
   Dim Ary As Variant
   Ary = Range("B5:M12").value2
End Sub

Thanks! But what if I would like to move the information from ary(1,1 to 12) to ary(2, 1 to 12)? I think I could simplify a lot of code I use if this is possible.

 

[Fluff]

You can do that like

Sub Macro3()
   Dim Ary As Variant
   Dim i As Long
   Ary = Range("B5:M12").value2
   For i = 1 To UBound(Ary, 2)
      Ary(2, i) = Ary(1, i)
   Next i
End Sub

But you will lose whatever was originally in Ary(2,x)