vba 将数组写入范围。只获取数组的第一个值

Writing an array to a range. Only getting first value of array

提问by gotmike

I am trying to write an array to a range and I have tried several ways but no matter what, I always get only the FIRST value of the array over and over again.

我正在尝试将数组写入一个范围，并且尝试了多种方法，但无论如何，我总是一遍又一遍地只获得数组的第一个值。

Here is the code:

这是代码：

Option Explicit

Sub test()

    ActiveWorkbook.Worksheets("Sheet1").Cells.Clear

    Dim arrayData() As Variant
    arrayData = Array("A", "B", "C", "D", "E")

    Dim rngTarget As Range
    Set rngTarget = ActiveWorkbook.Worksheets("Sheet1").Range("A1")

    'this doesn't work
    rngTarget.Resize(UBound(arrayData, 1), 1).Value = arrayData

    Dim rngTarget2 As Range
    Set rngTarget2 = ActiveWorkbook.Worksheets("Sheet1").Range(Cells(1, 5), Cells(UBound(arrayData, 1), 5))
    'this doesn't work either
    rngTarget2.Value = arrayData

End Sub

What I expect to see is:

我希望看到的是：

(Col A)     (Col E)
A           A
B           B
C           C
D           D
E           E

What I actually see is:

我实际看到的是：

(Col A)     (Col E)
A           A
A           A
A           A
A           A
A           A

What am I doing wrong here?

我在这里做错了什么？

I tried to follow Chip Pearson's suggestions, as found HERE

我试图按照芯片皮尔逊的建议，如发现这里

But no luck...

但没有运气...

Okay, so adding in the second part of this problem:

好的，所以添加这个问题的第二部分：

I have a 1D array with 8,061 elements that I am passing to the following function as such:

我有一个包含 8,061 个元素的一维数组，我将其传递给以下函数：

Call writeArrayData7(strTabName, arrayBucketData, 7)

Sub writeArrayData7(strSheetName As String, arrayData As Variant, intColToStart As Integer)

    Dim lngNextRow As Long
    lngNextRow = 1 ' hard-coded b/c in this instance we are just using row 1

    ' Select range for data
    Dim rngData As Range
    Set rngData = Sheets(strSheetName).Range(Cells(lngNextRow, intColToStart), Cells(lngNextRow - 1 + UBound(arrayData, 1), intColToStart))

    ' Save data to range
    Dim arrayDataTransposed As Variant
    arrayDataTransposed = Application.Transpose(arrayData)
    rngData = arrayDataTransposed

End Sub

So when I run this, the transpose function is properly converting into an:

所以当我运行这个时，转置函数正确地转换为：

Array(1 to 8061, 1 to 1)

The range appears to be a single column with 8,061 cells in Column G.

该范围似乎是一列，在 G 列中有 8,061 个单元格。

But I get the following error:

但我收到以下错误：

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

The error is thrown on the following line:

错误在以下行中抛出：

rngData = arrayDataTransposed

--- UPDATE ---

- - 更新 - -

So one thing I left out of my sample code (b/c I honestly didn't think it mattered) was that the contents of my array are actually formulas. Here is the line that I'm using in the actual live code:

所以我从我的示例代码中遗漏的一件事（老实说，我认为这并不重要）是我的数组的内容实际上是公式。这是我在实际实时代码中使用的行：

arrayData(i) = "=IFERROR(VLOOKUP($D" + CStr(i) + "," + strSheetName + "!$D:$F,3,FALSE),"")"

Well, what I found (with Excel Hero's help) was that the above statement didn't have the double sets of quotes required for a string, so I had to change to this instead:

好吧，我发现（在 Excel Hero 的帮助下）上面的语句没有字符串所需的双引号集，所以我不得不改为：

arrayBucketData(i) = "=IFERROR(VLOOKUP($D" + CStr(i) + "," + strSheetName + "!$D:$F,3,FALSE),"""")"

I can chalk that up to late-night bonehead coding.

我可以把它归结为深夜笨蛋编码。

However, one other thing I learned is that when I went back to run the full code is that it took FOREVER to paste the array to the range. This would ordinarily be a very simple task and happen quickly, so I was really confused.

然而，我学到的另一件事是，当我回去运行完整代码时，将数组粘贴到范围需要永远。这通常是一项非常简单的任务，而且很快就会发生，所以我真的很困惑。

After much debugging, I found that the issue came down to the fact that I was turning off all the alerts/calculations/etc and when I pasted in these formulas, the strSheetNamesheet was not there yet b/c I'm developing this code separate from the main file. Apparently, it throws up a dialog box when you paste the code in, but if you have all that stuff shut off, you can't see it but it REALLY slows everything down. It takes about 6mins to paste the range if those tabs are not there, and if they exist it takes seconds (maybe less). At any rate, to refine the code a bit further, I simply added a function that checks for the required sheet and if it doesn't exist, it adds the tab as a placeholder so the entire process doesn't slow to a crawl.

经过多次调试，我发现问题归结为我关闭了所有警报/计算/等，当我粘贴这些公式时，工作strSheetName表还没有 b/c 我正在单独开发此代码从主文件。显然，当您粘贴代码时它会弹出一个对话框，但是如果您关闭了所有这些东西，您将看不到它，但它确实会减慢一切速度。如果这些选项卡不存在，粘贴范围大约需要 6 分钟，如果它们存在，则需要几秒钟（可能更少）。无论如何，为了进一步细化代码，我只是添加了一个函数来检查所需的工作表，如果它不存在，它会添加选项卡作为占位符，这样整个过程就不会慢到爬行。

Thanks to everyone for their help! I hope this helps someone else down the road.

感谢大家的帮助！我希望这可以帮助其他人。

回答by Excel Hero

Do this:

做这个：

arrayData = Array("A", "B", "C", "D", "E")

[a1].Resize(UBound(arrayData)) = Application.Transpose(arrayData)

The important bit is the Transpose() function.

重要的一点是 Transpose() 函数。

But it is better to work with 2D arrays from the get go if you plan on writing them to the worksheet. As long as you define them as rows in the first rank and columns in the second, then no transposition is required.

但是，如果您打算将它们写入工作表，最好从一开始就使用 2D 数组。只要将它们定义为第一列中的行和第二列中的列，则不需要换位。

回答by Gary's Student

This:

这个：

Sub test()

    ActiveWorkbook.Worksheets("Sheet1").Cells.Clear

    Dim arrayData(1 To 5, 1 To 1) As Variant
    arrayData(1, 1) = "A"
    arrayData(2, 1) = "B"
    arrayData(3, 1) = "C"
    arrayData(4, 1) = "D"
    arrayData(5, 1) = "E"

    Dim rngTarget As Range
    Set rngTarget = ActiveWorkbook.Worksheets("Sheet1").Range("A1:A5")
    rngTarget = arrayData

End Sub

will produce:

将产生：

回答by Przemyslaw Remin

If I may expand the accepted answer, I propose:

如果我可以扩大接受的答案，我建议：

[a1].Resize(UBound(arrayData) - LBound(arrayData) + 1) = Application.Transpose(arrayData)

That would be the safe way. This will work no matter if you declare your array variable as:

那将是安全的方式。无论您是否将数组变量声明为：

Dim arrayData(0 to 2) 

or

或者

Dim arrayData(1 to 3) 

The accepted answer works only in the second case.

接受的答案仅适用于第二种情况。

The proposed way might be useful if the size of array is unknown and you declare your arrayData as:

如果数组的大小未知并且您将 arrayData 声明为：

Dim arrayData()