VBA Array to Range does not work

Question

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.

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
– Scott Craner, Commented Jun 15, 2017 at 18:45
Tanks. Transposing was all i needed.Range("A1:A9999") = Application.Transpose(Tiendas(164)) — Arthur Kersten
– Arthur Kersten, Commented Jun 15, 2017 at 19:07

ashleedawg · Accepted Answer · 2018-05-23 13:35:25Z

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.

answered May 23, 2018 at 13:35

ashleedawg

21.9k9 gold badges82 silver badges120 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA Array to Range does not work

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related