output variant into excel using vba

[MetLife]

Hi,

I have a 10x10 variant that I want to output to the workbook, but am stuck at the moment.


Dim xzy(1 To 100) As Variant


xzy(1) = 5
xzy(2) = 6
Range("A1") = Application.Transpose.xzy

Error = "Invalid number of arguments"

 

[JLGWhiz]

Hi,

I have a 10x10 variant that I want to output to the workbook, but am stuck at the moment.


Dim xzy(1 To 100) As Variant


xzy(1) = 5
xzy(2) = 6
Range("A1") = Application.Transpose.xzy

Error = "Invalid number of arguments"

try this syntax

Range("A1") = Application.Transpose(xzy)

Transpose is a function, not an object.

 

[MetLife]

If I do that it works but only outputs the first value "5" not the entire array

 

[Tetra201]

Try this:

Range("A1:A100") = Application.Transpose(xzy)

 

[MetLife]

That works, but my worry is that the size of the range is going to change. Is there a way to do this dynamically?

 

[Tetra201]

Is this what you need?

Range("A1:A" & YourRow) = Application.Transpose(xzy)

 

[MetLife]

Yes that works thanks again!

 

[Tetra201]

You are welcome.

 

[Rick Rothstein]

I have a 10x10 variant that I want to output to the workbook, but am stuck at the moment.

Dim xzy(1 To 100) As Variant

Why do you say you have a "10x10 variant" when your array is declared as one-dimensional?

Dim xzy(1 To 100) As Variant

xzy(1) = 5
xzy(2) = 6
Range("A1") = Application.Transpose.xzy

If you are going to fill the array that way (one element at a time), then if you declare the array this way...

Dim xzy(1 To 100, 1 To 1)

and fill it this way...

xzy(1, 1) = 5
xzy(2, 1) = 6
etc.

then you can do your assignment directly without having to Transpose anything...

Range("A1").Resize(UBound(xzy)) = xzy

Doing it this way, though, does take away your ability to use VB's array functions (such as Filter and Join) as they only work on one-dimensional arrays.