CODEWITHSUNDEEP
excelvba
Kent Pawar
Kent Pawar

Reputation: 2616

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

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.

Updates:

Here is what I got till now:

' Create a header: Write a list of Fields from Array into Range
Dim strTest As String
Dim strArray() As String
Dim firstCellOftheTargetRange, lastCellOftheTargetRange  as String

strTest = "PERIOD,YEAR,SCENARIO,ANALYTICS,ENTITY,ACC_NUMBER,AMOUNT"
strArray = Split(strTest, ",")

firstCell = "A1" ' First Cell Of the Target Range
lastCell = convertNumberToColumnName(UBound(strArray) + 1) & "1" ' last Cell Of the Target Range 

Worksheets("SheetName").Range(firstCell & ":" & lastCell) = strArray

This code doesn't use WorksheetFunction.Transpose as it outputs row-wise. If you require the output to be column-wise use the function as shown by @mehow. Thanks

Upvotes: 2

Views: 24279

Answers (3)

Patrick Lepelletier
Patrick Lepelletier

Reputation: 1652

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.

Upvotes: 0

gssi
gssi

Reputation: 5087

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.

Upvotes: 0

user2140173
user2140173

Reputation:

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

Upvotes: 8

Related Questions