Excel VBA-将2D数组的值分配给单元格范围 [英] Excel VBA - Assign Values of 2D Array to Range of Cells

问题描述

我正在尝试将尺寸为183x6的2D数组分配给新工作表，并使用该数组的值填充A1：G182中的空白单元格。作为参考，我的数组称为目录，而我要填充的空白表称为列表。

I'm trying to assign a 2D array of size 183x6 to a new sheet, populating the blank cells from A1:G182 with the values of the array. For reference, my array is called "Directory" and the empty sheet I want to populate is called "List".

我尝试了两种不同的方法，一种方法是将指定范围分配给数组，例如：

I've tried two different approaches, one by explicitly assigning the specified range to the array as such:

Worksheets("List").Range(Cells(1,1), Cells(UBound(Directory, 1) + 1, UBound(Directory, 2) + 1)) = Directory

并且还尝试遍历数组中的每个条目：

And also by trying to iterate through each entry in the array:

For i = 0 To UBound(Directory, 1)
    For j = 0 To UBound(Directory, 2)
        Worksheets("List").Range(Cells(i + 1, j + 1), Cells(i + 1, j + 1)) = Directory(i,j)
    Next j
Next i

情况下，我得到以下错误：

In both cases, I get the error:

Run-time error '1004':
Application-defined or object defined error.

有什么想法吗？感谢您的帮助。

Any ideas what could be happening? I appreciate your help.

推荐答案

尝试：

Worksheets("List").Range("A1").Resize(UBound(Directory, 1) + 1, UBound(Directory, 2) + 1).Value = Directory

或：

For i = 0 To UBound(Directory, 1)
    For j = 0 To UBound(Directory, 2)
        Worksheets("List").Range(Worksheets("List").Cells(i + 1, j + 1), Worksheets("List").Cells(i + 1, j + 1)) = Directory(i,j)
    Next j
Next i

这篇关于Excel VBA-将2D数组的值分配给单元格范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！