将单个值添加到单个列数组中。

I do not know the size of the array.
我不知道数组的大小。
The array is created from a column L on sheet "INPUT_MASTERDATA".
数组是由表“INPUT_MASTERDATA”上的列L创建的。

This is what I have so far:

这就是我目前所拥有的:

With Worksheets("INPUT_MASTERDATA")
    arrInputUniqueItems = .Range("L2", .Range("L" & Rows.Count).End(xlUp))
End With

I would like to include the value "x" in the above array.

我想在上面的数组中包含值“x”。

Sample data from Range L on Worksheet "INPUT_MASTERDATA"

在工作表“INPUT_MASTERDATA”上的范围L的示例数据

These all goes into the array just fine. Now I would like to add the value "x". So the array afterwards looks like this:

这些都进入了数组。现在我想添加值x。之后的数组是这样的

Any ideas or help is highly appreciated! Thank you in advance.

非常感谢您的建议和帮助!提前谢谢你。

2 个解决方案

#1

If you are going to be dynamically adding items to your collection you should consider a better suitable data type like Collection. Array's are not supposed by resized after their dimensions are specified.

如果要动态地向集合添加项，您应该考虑一种更好的数据类型，比如collection。数组的尺寸被指定后，不应该被重新调整大小。

So... consider creating an array from range in one go, then loading that into a Collection and then you can add more items to the collection. The reason you want to use the array is to load the entire range into memory is going to be faster than iterating a Range and adding each cell to the collection directly.

所以…考虑一次从范围创建一个数组，然后将其加载到集合中，然后可以向集合添加更多的项。使用数组的原因是将整个范围加载到内存中要比迭代一个范围并直接将每个单元格添加到集合中要快。

Sub Main()

    [L1] = "header"
    [L2] = "R83711850"
    [L3] = "1210221340"
    [L4] = "1210223342"
    [L5] = "R83711181"
    [L6] = "R83711931"

    Dim lastRow As Long
    lastRow = Range("L" & Rows.Count).End(xlUp).Row

    Dim v As Variant
    Dim c As New Collection

    Dim arr As Variant
    arr = Range("L2:L" & lastRow).Value

    For Each v In arr
        c.Add v
    Next

    ' then if you ever need to add more just add it to the collection
    c.Add "new value"

    ' print to confirm
    For Each v In c
        Debug.Print v
    Next

End Sub

prints

打印

in the Immediate Window CTRL+G

在当前窗口中按CTRL+G

#2

You can do this easily enough without resorting to further loops and/or other methods.

您可以很容易地做到这一点，而不必求助于进一步的循环和/或其他方法。

Simply use a range 1 cell longer than the end of the range of interest, or
简单地使用范围1单元格，比感兴趣范围的结束时间长
Use ReDim on a 1D array (of course you can and should resize arrays)
在一维数组上使用重拨(当然您可以并且应该调整数组的大小)

code 1

代码1

Sub Method1_2D()
Dim arrInputUniqueItems
With Worksheets("INPUT_MASTERDATA")
    arrInputUniqueItems = .Range("L2", .Range("L" & Rows.Count).End(xlUp).Offset(1, 0))
    arrInputUniqueItems(UBound(arrInputUniqueItems), 1) = "X"
End With
End Sub

code 2

代码2

Sub Method2_1D()
Dim arrInputUniqueItems
With Worksheets("INPUT_MASTERDATA")
    arrInputUniqueItems = Application.Transpose(.Range("L2", .Range("L" & Rows.Count).End(xlUp)))
    ReDim Preserve arrInputUniqueItems(UBound(arrInputUniqueItems))
    arrInputUniqueItems(UBound(arrInputUniqueItems)) = "X"
End With
End Sub

#1