Copying a String Array into a Range of cells (in MS Excel using VBA)

3
votes

I can use a For construct to loop through the string array elements and copy their contents into the individual cells of the range; but is there a simpler way to directly copy the string array items into the Range?

The question Range to string array solves the exact opposite of what I am trying to do.

3
excelvba

3 Answers

8
votes

Like this

Sub StringArrayToRange()

    Dim strArr(3) As String
    strArr(0) = "one"
    strArr(1) = "two"
    strArr(2) = "three"

    Range("A1:A" & UBound(strArr) + 1) = WorksheetFunction.Transpose(strArr)

End Sub

also, this for more examples and tutorial

EDIT:
this documentation explains why the WorksheetFunction.Transpose was used

0
votes

Do you really need the array in a cell? Remember, you can define a variable equal to an array value: var1 = {v11, v12, v13;v21, v22, v23}

then put "=INDEX( var1, 2, 2)" in any to cell to get value v22.

0
votes

i had an other way doing this:

Dim Arr()
With ThisWorkbook.Sheets("MassHeals")
  Arr = Array("1", "2", "3", "4")
  .Cells(1, 1).Resize(1, 4).value2 = Arr 
End With

The array is set in one line, and i do not use transpose.