CODEWITHSUNDEEP
arraysvbaexcelprinting
Crash
Crash

Reputation: 15

Excel VBA Print Array to Worksheet

Based on an example I found on this site I made the following procedure. It prints only the first element of the array into the entire range instead of printing each element into each cell of the range. Do you have any idea what I'm doing wrong? Thanks, Crash

i = 2
Do Until Cells(i, 1) = "" 'loops through IDs in 1st column of spreadsheet
    If Cells(i, 1) > "" Then 'if it finds an ID
        GoSub CommentsColor 'sub that calculates a color -> thisColor
    End If
    ReDim Preserve colors(i - 2) 'start array at zero
    colors(i - 2) = thisColor 'populate array
    thisColor = "" 'clear variable
    i = i + 1 'go to next ID in 1st column of spreadsheet
Loop

'set range
Set colorData = ActiveWorkbook.Worksheets("Movement_Data").Range(Cells(2, thisCol), Cells(i - 1, thisCol))
colorData.Value = colors 'print array to worksheet

Upvotes: 1

Views: 3906

Answers (1)

user4039065
user4039065

Reputation:

  1. Your range and cells references do not specifically belong to that worksheet; they belong to activesheet.

    with ActiveWorkbook.Worksheets("Movement_Data")
    Set colorData = .Range(.Cells(2, thisCol), .Cells(i - 1, thisCol))
end with

  2. Transpose the array to match your destination.

    colorData = application.transpose(colors) 'print array to worksheet

  3. Better to simply resize the destination according to the array.

    ActiveWorkbook.Worksheets("Movement_Data").Cells(2, thisCol).resize(ubound(colors)+1, 1) = application.transpose(colors)

Upvotes: 2

Related Questions