Assign multi dimensional array to a range.

[Sorwen]

Is it possible to pass a multi dimensional array to a range of cells? I know you can pass a single array to a range.

Sub ArrayToCells(CurrentData As Variant)
    Range(Cells(1, 1), Cells(5, 5)) = CurrentData
End Sub

If I use the above code with a multi dimensional array it will only assign the last array to the first row. Is there a way to do this?

-I hate Microsoft!
-Forever and always forward.
-My kingdom for a edit button!

 

[DaveInIowa]

Assigning a range to an array creates a 2 dimension variant array even if the range is a single row or column. This is the type of array you should be using to reverse the process when assigning back to the range. I'm guessing that the result you're seeing with you example is that CurrentData has only a single row. One of the nice things VBA does for you is setting the lower array bounds to 1, so there's a 1-to-1 relationship between the worksheets rows and columns.

Sub ObserveDimensions()
    Dim varr As Variant
    
    varr = Range("A1:G10")
    
    MsgBox "UBound Rows: " & UBound(varr, 1) & vbCrLf & "UBound Columns: " & UBound(varr, 2)
    
    ' Still a 2 dimension array, but the 1st dimension = 1
    varr = Range("A1:G1")
    
    MsgBox "UBound Rows: " & UBound(varr, 1) & vbCrLf & "UBound Columns: " & UBound(varr, 2)
    
End Sub

 

[Sorwen]

It was my stupid. It is working fine now. Thanks.

-I hate Microsoft!
-Forever and always forward.
-My kingdom for a edit button!