VBA将数组赋值到Excel工作表区域仅粘贴第一个值如何解决

存在的问题 #

你的代码共有3处核心错误：

• 数组扩容语法错误：循环内的ReDim Preserve Array1未指定数组新上界，数组无法正确扩容，始终仅能存储1个元素。
• 数组方向与写入区域不匹配：VBA默认的一维数组是行方向数组，如果直接赋值给纵向的单列区域，Excel仅能识别第一个元素。
• 赋值区域范围不匹配：直接给单个单元格Range("A1")赋值数组，Excel默认仅填充该单元格，不会自动扩展填充范围。

修正方案 #

方案1：将数组写入A1:A10纵向区域（常用场景） #

需要先正确扩容数组，再用WorksheetFunction.Transpose转置数组为纵向，同时指定和数组大小一致的写入区域：

Dim Array1()
Dim i As Integer

For i = 0 To 9
   ' 正确指定数组扩容的上界
   ReDim Preserve Array1(0 To i)
   Array1(i) = Int((6 * Rnd) + 1)
Next

' 转置数组为纵向，同时指定对应大小的写入区域
ws.Range("A1:A10").Value = WorksheetFunction.Transpose(Array1)

方案2：将数组写入A1:J1横向区域 #

如果需要横向填充，不需要转置，直接指定对应横向区域即可：

Dim Array1()
Dim i As Integer

For i = 0 To 9
   ReDim Preserve Array1(0 To i)
   Array1(i) = Int((6 * Rnd) + 1)
Next

' 横向区域直接赋值即可
ws.Range("A1:J1").Value = Array1

优化建议 #

如果数组大小提前可知，尽量不要在循环内反复ReDim Preserve，直接提前定义数组大小即可，运行效率更高：

' 提前定义固定大小的数组，避免循环内反复扩容
Dim Array1(0 To 9)
Dim i As Integer

For i = 0 To 9
   Array1(i) = Int((6 * Rnd) + 1)
Next

ws.Range("A1:A10").Value = WorksheetFunction.Transpose(Array1)

内容的提问来源于stack exchange，提问作者TourEiffel