将数组写入范围.只获取数组的第一个值 [英] Writing an array to a range. Only getting first value of array

问题描述

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

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.

代码如下:

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

我希望看到的是:

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

我实际看到的是:

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

我在这里做错了什么?

我尝试遵循 Chip Pearson 的建议，如发现 HERE

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

但没有运气...

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

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

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

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

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)

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

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

但我收到以下错误:

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

错误在下面一行被抛出:

The error is thrown on the following line:

rngData = arrayDataTransposed

--- 更新---

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

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),"")"

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

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:

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

我可以把这归结为深夜笨蛋编码.

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

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

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.

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

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 strSheetName sheet 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.

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

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

推荐答案

这样做:

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

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

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

The important bit is the Transpose() function.

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

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.

这篇关于将数组写入范围.只获取数组的第一个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！