0

I want to populate a range of cells with the elements of an array.

The only method I know is limited to populating cells along one row, for example:

Sub testArrayOne()

    Dim arrHeader As Variant
    arrHeader = Array("1", "", "2", "", "3")

    Sheets(2).range("A1:A5").Value = arrHeader

End Sub

But I'm wondering how to populate a range that has multiple rows, or only the dimensions of one column. My confusion stems from my lack of understanding of how arrays work in general and in VBA.

With this code I was expecting that cell A1 would have the value 1, and cell C3 the value 9.

Sub testArrayTwo()

    Dim arrHeader As Variant
    arrHeader = Array("1", "2", "3", "4", _
                      "5", "6", "7", "8", "9")

    Sheets(2).range("A1:C3").Value = arrHeader

End Sub

Instead this is what I got:

Table in Excel

I can see that only the first three values were used to populate the first row, and this process was repeated for the rest of the rows, but I don't know why that happens and what I must do to get the desired output.

Edit: as reference, the solution (adapted from here):

Sub testArrayThree()

    Dim arrHeader As Variant
    arrHeader = [{1, 2, 3; 4, 5, 6; 7, 8, 9}]
 
    Sheets(2).Range("A1:C3").Value = arrHeader

End Sub
Alice. M.
  • 1
  • 1
  • It happens because your array is basically a vector (an array with one dimension, say a row). Every comma there signals VBA that the next value is placed in the next slot of the dimension. In order to populate the cells the way you want, you have to create an array with 2 dimensions (say rows and columns). I don't recall a way to hardcode an array of 2 dimensions using punctuation (like you can do in formulas using \ and .). What's the big picture here? What are you trying to achieve? – Evil Blue Monkey May 31 '23 at 08:17
  • @EvilBlueMonkey thanks for your comment. My goal is to create a template header for a sheet. So far I've used "range.Value =" or "cell.Value =" for every single cell, but because the header is quite "complex" (3 rows and 8 columns), I thought an array would be neater. – Alice. M. May 31 '23 at 08:24
  • Why not create a range in an hidden sheet and then copy the header accordingly to your need? – Evil Blue Monkey May 31 '23 at 08:31
  • 2
    @EvilBlueMonkey as a side note, you apparently can hardcode a two dimensional array by adding some square brackets and semicolons to separate the rows. Have a look here: https://stackoverflow.com/a/37584058/9852011 (tested it, it works). – andrewb May 31 '23 at 08:33
  • @andrewb I'm new to Stack Overflow, if I want to post the solution that worked for me so that others can benefit from it, should I edit my own post, or post an actual answer and mark it as accepted? – Alice. M. May 31 '23 at 08:45
  • You can answer your own question. It is apparently encouraged. Or you can just leave it as is. The link to the answer I posted will appear on the right in the "Linked" section. Either way should be fine. *You can read about answering your questions here: https://meta.stackoverflow.com/questions/250204/can-you-answer-your-own-questions-on-stack-overflow – andrewb May 31 '23 at 08:50
  • `Sub testArrayOne() Dim arrHeader As Variant arrHeader = Array("1", "", "2", "", "3") Set rng = Sheets(1).Range("A1:A5") ' Sheets(2).Range("A1:A5").Value = arrHeader rng = ArrSpread1in2D(arrHeader, rng) End Sub Function ArrSpread1in2D(ByRef arr As Variant, ByVal rng As Range) Dim count As Long For Each x In rng count = count + 1 x.Value = arr(count - 1) Next x End Function` – IvanSTV May 31 '23 at 13:09

0 Answers0