Hi all,

I want to write more than 29 array varibles (defined in a my VBA code)
in a workbook range. Is there a way to accomplish this?

Here is my sample code (check my remark):

Sub ArrayTest()
Dim Array1 As Variant
Dim Array2 As Variant
Dim CompleteArray As Variant

Array1 = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15)
Array2 = Array(16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28,
29, 30)
CompleteArray = Array(Array1, Array2)

Range("A1:AD1").Value = (CompleteArray) 'Result: Range A1 & B2 is
empty while the rest is #N/A
End Sub

Thanks for any feedback!

- Bas

One way:

Dim CompleteArray As Variant
CompleteArray = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, _
14, 15, 16, 17, 18, 19, 20, 21, 22, 23, _
24, 25, 26, 27, 28, 29, 30)
Range("A1:AD1").Value = CompleteArray

By making an array of arrays, CompleteArray is two dimensional rather
than a single array.

Just for illustration, take a look at

Public Sub ArrayTest()
Dim Array1 As Variant
Dim Array2 As Variant
Dim CompleteArray As Variant

Array1 = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15)
Array2 = Array(16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, _
28, 29, 30)
CompleteArray = Array(Array1, Array2)

Range("A1:O2").Value = Application.Transpose( _
Application.Transpose(CompleteArray))
End Sub




In article <(E-Mail Removed)>,
(E-Mail Removed) wrote:

> Hi all,
>
> I want to write more than 29 array varibles (defined in a my VBA code)
> in a workbook range. Is there a way to accomplish this?
>
> Here is my sample code (check my remark):
>
> Sub ArrayTest()
> Dim Array1 As Variant
> Dim Array2 As Variant
> Dim CompleteArray As Variant
>
> Array1 = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15)
> Array2 = Array(16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28,
> 29, 30)
> CompleteArray = Array(Array1, Array2)
>
> Range("A1:AD1").Value = (CompleteArray) 'Result: Range A1 & B2 is
> empty while the rest is #N/A
> End Sub
>
> Thanks for any feedback!
>
> - Bas

Wow! :-o
I thought, from the beginning of my work, that the maximum arguments
from an array was 29.
Mea culpa, and thanks very much!

On Apr 15, 12:23 pm, JE McGimpsey <jemcgimp...@mvps.org> wrote:
> One way:
>
> Dim CompleteArray As Variant
> CompleteArray = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, _
> 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, _
> 24, 25, 26, 27, 28, 29, 30)
> Range("A1:AD1").Value = CompleteArray
>
> By making an array of arrays, CompleteArray is two dimensional rather
> than a single array.
>
> Just for illustration, take a look at
>
> Public Sub ArrayTest()
> Dim Array1 As Variant
> Dim Array2 As Variant
> Dim CompleteArray As Variant
>
> Array1 = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15)
> Array2 = Array(16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, _
> 28, 29, 30)
> CompleteArray = Array(Array1, Array2)
>
> Range("A1:O2").Value = Application.Transpose( _
> Application.Transpose(CompleteArray))
> End Sub
>
> In article <1176649898.581194.125...@e65g2000hsc.googlegroups.com>,
>
> VirtualR...@gmail.com wrote:
> > Hi all,
>
> > I want to write more than 29 array varibles (defined in a my VBA code)
> > in a workbook range. Is there a way to accomplish this?
>
> > Here is my sample code (check my remark):
>
> > Sub ArrayTest()
> > Dim Array1 As Variant
> > Dim Array2 As Variant
> > Dim CompleteArray As Variant
>
> > Array1 = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15)
> > Array2 = Array(16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28,
> > 29, 30)
> > CompleteArray = Array(Array1, Array2)
>
> > Range("A1:AD1").Value = (CompleteArray) 'Result: Range A1 & B2 is
> > empty while the rest is #N/A
> > End Sub
>
> > Thanks for any feedback!
>
> > - Bas