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

Question

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

score 8 · Accepted Answer · 2013-06-07 12:05:59Z

8

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

edited Jun 7, 2013 at 12:05

answered Jun 7, 2013 at 11:55

user2140173

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

6 Comments

Kent Pawar Over a year ago

Hi mehow, Thanks for the link and code. I tweaked around a bit and don't quite get why WorksheetFunction.Transpose is required? My sample seems to work fine without the function

Kent Pawar Over a year ago

Hi @Santosh - how does WorksheetFunction.Transpose help in the above code. The sample I posted seems to work fine without it.. Does it help with compatibility across older versions?

user2140173 Over a year ago

@KentPawar please refer to the link in my answer, but short story shorter (hehe) one dimensional array is in a horizontal view so you are converting it into a vertical to be technically correct

Kent Pawar Over a year ago

Thanks @mehow.. Looking at it now.

Kent Pawar Over a year ago

Argh.. I just realized your code requires WorksheetFunction.Transpose as it is output-ing into a column while my code outputs row-wise hehe.. I need a break :p. Thanks, marked it as answered

|

gssi · Accepted Answer · 2013-06-07 12:26:34Z

0

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.

answered Jun 7, 2013 at 12:26

gssi

5,0875 gold badges25 silver badges23 bronze badges

1 Comment

Kent Pawar Over a year ago

Hi @gssi, thanks. I needed the array items into a individual cells separately, but I want to do it using VBA and the target sheet is created dynamically so that won't work.

Patrick Lepelletier · Accepted Answer · 2018-04-06 00:04:59Z

0

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.

answered Apr 6, 2018 at 0:04

Patrick Lepelletier

1,6522 gold badges19 silver badges24 bronze badges

1 Comment

SomeDude Over a year ago

Transpose is needed only when you write a one dimension array in a column, not when you write it in a row as you do here

Collectives™ on Stack Overflow

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

Updates:

3 Answers 3

6 Comments

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Updates:

3 Answers 3

6 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related