Excel VBA打印数组到工作表 [英] 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

推荐答案

1. 您的范围和单元格引用不专门属于该工作表；它们属于活动表.

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

转置数组以匹配您的目的地.

Transpose the array to match your destination.

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

更好地根据数组调整目标大小.

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)

这篇关于Excel VBA打印数组到工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！