vba multiD array to range

Question

I'm having an issue with writing an 4D Array to a range in Excel.

My Array Looks like this:

varArray(0)
- varArray(0)(0) "test01"
- varArray(0)(1) "test02"
- varArray(0)(2) "test03"
- varArray(0)(3) "test04"

varArray(1)
- varArray(1)(0) "test11"
- varArray(1)(1) "test12"
- varArray(1)(2) "test13"
- varArray(1)(3) "test14"

There will be more than only 2 "Items" in the Array in the end but for understanding I displayded 2 of them.

I tried it with transpose but I coudl not Access the subitems

Range("A" & CellIndex) = Application.Transpose(varArray(0,1))

does not work :S

Output should look like this(write in to a range):

       A       B      C      D
1    test01 test02 test03 test04
2    test11 test12 test13 test14

Can anyone assist me on this?

"does not work" -- you get an error, eh? What's the error? Can you structure your array differently e.g., varArray(0,0)? — David Zemens
– David Zemens, Commented Mar 24, 2017 at 14:41
Show us what you have in your code and what the output currently is so that we can help troubleshoot what you've already got. — Mike
– Mike, Commented Mar 24, 2017 at 14:58

CallumDA · Accepted Answer · 2017-03-24 14:45:45Z

1

You can use Application.Transpose twice. This will output to the worksheet in columns A:D

Sub CreateArray()
    Dim varArray As Variant
    varArray = Array(Array(1, 2, 3, 4), Array(11, 12, 13, 14))

    For i = 0 To 1
        ThisWorkbook.Worksheets("Sheet1").Range("A1:D1").Offset(i, 0).Value = Application.Transpose(Application.Transpose(varArray(i)))
    Next i
End Sub

answered Mar 24, 2017 at 14:45

CallumDA

12.1k6 gold badges33 silver badges52 bronze badges

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

1 Comment

miekW Over a year ago

This is it! Thanky you very much, the solution works perfect. the Offset was the missing bit

Pspl · Accepted Answer · 2017-03-24 14:48:39Z

1

Try:

Dim varArray(0 To 1, 0 To 3) As String
    varArray(0, 0) = "test01"
    varArray(0, 1) = "test02"
    varArray(0, 2) = "test03"
    varArray(0, 3) = "test04"
    varArray(1, 0) = "test11"
    varArray(1, 1) = "test12"
    varArray(1, 2) = "test13"
    varArray(1, 3) = "test14"
Range("A1:D2") = varArray()
Range("F1:G4") = Application.Transpose(varArray())

I think the output you want is simply your array, not your transposed array. However I put the two outputs on the code. Feel free to change the adresses...

answered Mar 24, 2017 at 14:48

Pspl

1,47413 silver badges26 bronze badges

2 Comments

CallumDA Over a year ago

I think this array has a different structure to that of OPs. OP has a 1d array containing arrays, rather than a simple 2d array

Pspl Over a year ago

Yes. This is not an array of arrays... If you want to transpose it you have to use the operator twice (one inside another)... That's why I suggest this simpler way...

Vityata · Accepted Answer · 2017-03-24 14:49:10Z

1

Do you want something like this:

Option Explicit

Public Sub TestMe()

    Dim varArray            As Variant
    Dim lCounter            As Long
    Dim lCounter2           As Long
    Dim rngCell             As Range

    varArray = Array(Array(1, 2, 3, 4), Array(11, 12, 13, 14))
    Set rngCell = Cells(1, 1)

    For lCounter = LBound(varArray) To UBound(varArray)
        For lCounter2 = LBound(varArray(lCounter)) To UBound(varArray(lCounter))

            Debug.Print varArray(lCounter)(lCounter2)

            rngCell = varArray(lCounter)(lCounter2)
            Set rngCell = rngCell.Offset(0, 1)

        Next lCounter2
        Debug.Print "-----------"
        Set rngCell = Cells(rngCell.Row + 1, 1)

    Next lCounter

End Sub

The result in the immediate window is this one:

 1 
 2 
 3 
 4 
-----------
 11 
 12 
 13 
 14 
-----------

From this output, you can easily come to your desired one.

edited Mar 24, 2017 at 14:49

answered Mar 24, 2017 at 14:42

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

1 Comment

miekW Over a year ago

Hi Vityata, I want to write the Data into a Range. Each Array index shoudl be 1 row so for the 2 ones above it woudl be: item 1 A1:D1 and item 2 A2:D2

David Zemens · Accepted Answer · 2017-03-24 14:50:10Z

1

You're trying to transpose a single item in the array:

Application.Transpose(varArray(0,1))

Also, this array isn't indexed in such a manner. You could have varArray(0)(1), but you don't have varArray(0,1).

Try this:

Dim x as Long
For x = LBound(varArray) To UBound(varArray)
    Range("A1").Resize(1, UBound(varArray(x)) + 1).Offset(x) = Application.Transpose(Application.Transpose(varArray(x)))

Next

answered Mar 24, 2017 at 14:50

David Zemens

53.8k12 gold badges86 silver badges132 bronze badges

Collectives™ on Stack Overflow

vba multiD array to range

4 Answers 4

1 Comment

2 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related