1

I have a array of arrays. The array is 999 elements big and every sub-array has 9999 elements.

At the last part of the code i try to y the sub-arrary 164 to Range("A1:A9999") but it does not work.

Here is part of the code:

MsgBox (UBound(Tiendas(164), 1))
ActiveSheet.Range("B1") = CStr(UBound(Tiendas(164), 1))
ActiveSheet.Range("B2") = Tiendas(164)(33)
Range("A1:A9999") = Tiendas(164)

My MsgBox returns 9999. B1 returns 9999. B2 returns value in element 33. OK. But Range("A1:A9999") = Tiendas(164) is empty.

Arthur Kersten
  • 11
  • 4
  • 2
    1 the array is probably horizantal, so you will need to transpose the array. 2 try adding `.Value` to the end of `Range("A1:A9999")` so `Range("A1:A9999") .Value = ...` if that does not work then see number 1. – Scott Craner Jun 15 '17 at 18:45
  • Tanks. Transposing was all i needed.Range("A1:A9999") = Application.Transpose(Tiendas(164)) – Arthur Kersten Jun 15 '17 at 19:07

1 Answers1

0

The array and range need to be sized identically in order for the paste to work efficiency (ie., without looping through elements and cells).

This procedure provides a dynamic way of accomplishing that, given only the [one- or two-dimensional] array, and single-cell range reference representing the top-left corner where the data should be output.

Sub Array2Range(arr, destTL As Range)
    'dumps [arr] (1D/2D) onto a sheet where [destTL] is the top-left output cell.
    destTL.Resize(UBound(arr, 1) - LBound(arr, 1) + 1, _
        UBound(arr, 2) - LBound(arr, 2) + 1) = arr
End Sub

See further explanation and examples at my answer here.

ashleedawg
  • 20,365
  • 9
  • 72
  • 105