1

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
Crash
  • 15
  • 1
  • 5
  • 1
    http://www.cpearson.com/excel/ArraysAndRanges.aspx You might need Application.Transpose(colors) . Not sure of the dimensions. See link. – QHarr May 12 '18 at 18:22
  • I can't mark your comment at correct but it is. All I changed was Application.Transpose(colors) and it worked. – Crash May 12 '18 at 22:35
  • Not a problem ;-) – QHarr May 13 '18 at 05:01

1 Answers1

2

  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)
  • [Is the . in .Range necessary when defined by .Cells?](https://stackoverflow.com/questions/36368220/is-the-in-range-necessary-when-defined-by-cells) –  May 12 '18 at 18:47
  • Good point about activeworkbook... not being necessary. I added that in to make sure I was looking at the correct range but it is not necessary since I was running this on the active worksheet only. In the end all I needed was Application.Transpose(colors) . I didn't think I'd have to transpose a 1 dimensional array. – Crash May 12 '18 at 22:37
  • 1
    A 1-D array equates to one row and many columns. To feed it into one column and many rows, you need transpose. –  May 12 '18 at 22:40