将一系列单元格添加到集合中，然后调用集合的每个项目以在 excel 中的 vba 中使用它们
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/41015470/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Add a range of cells into a collection then call each item of the collection to use them in vba from excel
提问by Hu?nh Tùng
I am trying to add a range of cells in VBA then use each item of the collection to execute my work. I managed to store the data into the collection but failed to call it by using Collection.Item and debug.Print to show it onto the Immediate window. But this is work if I only store a cell into the collection. Here is my VBA code:
我试图在 VBA 中添加一系列单元格，然后使用集合的每个项目来执行我的工作。我设法将数据存储到集合中，但未能通过使用 Collection.Item 和 debug.Print 将其显示到立即窗口中来调用它。但如果我只将一个单元格存储到集合中，这是可行的。这是我的 VBA 代码：
lastRowIndex = Cells(Rows.Count, 1).End(xlUp).Row
Set PGname = New Collection
Set HScode = New Collection
For i = 1 To lastRowIndex
PGname.Add Range((Cells(i, 1)), (Cells(i, 2)))
HScode.Add (Cells(i, 2))
Next i
Debug.Print PGname.Item(1) 'this is not work
Debug.Print HScode.Item(1) 'this is work
Thank you for your time.
感谢您的时间。
回答by Vityata
Try this:
尝试这个：
Option Explicit
Public Sub Test()
Dim i As Long
Dim lastRowIndex As Long
Dim PGname As Object
Dim HScode As Object
lastRowIndex = 10
Set PGname = New Collection
Set HScode = New Collection
For i = 1 To lastRowIndex
PGname.Add Range((Cells(i, 1)), (Cells(i, 2)))
HScode.Add (Cells(i, 2))
Next i
Debug.Print PGname.Item(1).Address
Debug.Print PGname.Item(1)(1)(1) 'this would print you cell A1
End Sub
You get an error, because you were trying to print a range of more than once cell. Take a look at the two debug.prints. Put some value in A1 to make sure what it prints.
您收到错误消息，因为您试图打印多个单元格的范围。看看这两个 debug.prints。在 A1 中放置一些值以确保它打印的内容。
