如何使用 VBA 将范围（多行和多列）中的数据填充到列表框
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10763310/
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
How to populate data from a range (multiple rows and columns) to listbox with VBA
提问by NCC
I am having trouble with how to put the data from the range with multiple columns and rows to a listbox.
我在如何将具有多列和多行的范围内的数据放入列表框时遇到了问题。
Assume I have a range rng which multiple columns and rows I tried:
假设我有一个范围 rng，我尝试了多个列和行：
If I tried addItem rng(i,j) then everything would be in 1 column.
如果我尝试 addItem rng(i,j) 那么一切都将在 1 列中。
I also tried .list but it did not work either.
我也尝试过 .list 但它也不起作用。
回答by Siddharth Rout
Is this what you are trying?
这是你正在尝试的吗？
Option Explicit
Private Sub CommandButton1_Click()
Dim ws As Worksheet
Dim rng As Range
Dim i As Long, j As Long, rw As Long
Dim Myarray() As String
'~~> Change your sheetname here
Set ws = Sheets("Sheet1")
'~~> Set you relevant range here
Set rng = ws.Range("A1:E5")
With Me.ListBox1
.Clear
.ColumnHeads = False
.ColumnCount = rng.Columns.Count
ReDim Myarray(rng.Rows.Count, rng.Columns.Count)
rw = 0
For i = 1 To rng.Rows.Count
For j = 0 To rng.Columns.Count
Myarray(rw, j) = rng.Cells(i, j + 1)
Next
rw = rw + 1
Next
.List = Myarray
'~~> Set the widths of the column here. Ex: For 5 Columns
'~~> Change as Applicable
.ColumnWidths = "50;50;50;50;50"
.TopIndex = 0
End With
End Sub
SNAPSHOT
快照
回答by Sudhakar B
You can use foreach to add items to the list box
您可以使用 foreach 将项目添加到列表框中
ActiveSheet.Shapes("lstSample").Select
Dim currRange As Range
With Selection
For Each currRange In Range("yourRange")
.AddItem currRange.Value
Next
End With
For each itself iterates through each row and column in your range.
对于每个本身，迭代范围内的每一行和每一列。
回答by Sudhakar B
I am assuming you want to populate 3 columns
我假设你想填充 3 列
Dim currRange As Range
Dim i As Integer
With Selection
For Each currRange In Range("yourRange")
i = i + 1
If i = 1 Then .AddItem cell.Value
If i = 2 Then .List(.ListCount - 1, 1) = "1"
If i = 3 Then
.List(.ListCount - 1, 2) = cell.Offset(0, 2).Value
i = 0
End If
Next
End With
I am assuming you have 3 columns.
我假设你有 3 列。
