使用 VBA 将值插入 excel 表的正确方法是什么？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12081624/
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
What is the proper way to insert values into an excel table using VBA?
提问by Ashton Sheets
I need to be able to insert a large set of values into an empty table in excel using VBA code. Here's how the code works so far.
我需要能够使用 VBA 代码在 excel 中的空表中插入大量值。到目前为止，代码的工作方式如下。
First, the user inputs a value into a userform. The code then clears the table then goes and finds a range of numbers based on lookup criteria already in the code. The retrieved data is all contained within a single column and stored like an array.
首先，用户在用户表单中输入一个值。然后代码清除表，然后根据代码中已有的查找条件查找一系列数字。检索到的数据都包含在单个列中，并像数组一样存储。
From here, I need to place all of the values into a certain column in the table (Policy #), thus expanding the table rows to be however many rows were in the set of retrieved data. (I do have the count already stored separately as "AC" if it's needed) The column's header I want to insert into is "Policy #".
从这里开始，我需要将所有值放入表中的某个列（策略 #），从而将表行扩展为检索数据集中的行数。（如果需要，我确实已经将计数单独存储为“AC”）我要插入的列标题是“Policy #”。
Keeping in mind that there is only a blank row in the table at the present time of the code, how can I insert the data properly? I've tried
请记住，代码当前时间表中只有一个空白行，如何正确插入数据？我试过了
range("commissionstatement[Policy #]").value = Als
but that doesnt work. Als is the array of values by the way. Usually to insert the array I have to insert into a range equal in size which is why I took the row count as AC already.
但这不起作用。顺便说一下，Als 是值的数组。通常要插入数组，我必须插入一个大小相等的范围，这就是为什么我已经将行数作为 AC 的原因。
I've also tried using range("commissionstatement").listobject.listrowsbut that doesnt work either.
我也试过使用，range("commissionstatement").listobject.listrows但这也不起作用。
Any advice? Am I going to need to insert a number of rows into the table equal to the number of data im adding, before I actually place the data in there like this...
有什么建议吗？我是否需要在表中插入与我添加的数据数量相等的行数，然后才能像这样实际将数据放入其中...
range("commissionstatement").listobject.listrows.add ()
followed by inserting the data?
然后插入数据？
Let me know if more info is needed. Thanks!
如果需要更多信息，请告诉我。谢谢！
回答by chris neilsen
Assuming you are using Excel 2010, try this (may not work with earlier versions of Excel)
假设您使用的是 Excel 2010，请尝试此操作（可能不适用于早期版本的 Excel）
Sub AddToList()
Dim lo As ListObject
Dim ws As Worksheet
Dim Als() As Variant
Dim rng As Range
Set ws = ActiveSheet
' Get reference to table
Set lo = ws.ListObjects("MyTable") ' <--- Update this with your table name
If lo.InsertRowRange Is Nothing Then
' List already has data
Set rng = lo.ListRows.Add.Range
Else
' List is empty
Set rng = lo.InsertRowRange
End If
' *** Remove one of these two lines ***
' If Als is a 1 dimensional array
rng.Cells(1, lo.ListColumns("Policy #").Index).Resize(UBound(Als) - LBound(Als) + 1, 1) = Application.Transpose(Als)
' If Als is 2 dimensional array (1 to rows, 1 to 1)
rng.Cells(1, lo.ListColumns("Policy #").Index).Resize(UBound(Als, 1), 1) = Als
End Sub
回答by Jordi
This worked for me. I'm also filling an empty ListObject with data from an array. For this to work the array matriumust be a 2-dimensional array.
这对我有用。我还用数组中的数据填充了一个空的 ListObject。为此，数组matriu必须是二维数组。
Dim matriu() As Variant
Dim ls As ListObject
Set ls = Hoja1.ListObjects(1)
' Retrieve data
matriu = Hoja1.Range("Origen")
' Erease all rows of the ListObject, if needed
ls.DataBodyRange.Delete
' This line is needed in order for the next line
' not to throw an error if the listobject is empty.
ls.ListRows.Add
' Fill the ListObject with all data in one operation to reduce execution time
Range(ls.DataBodyRange.Cells(1, 1), _
ls.DataBodyRange.Cells(UBound(matriu, 1), UBound(matriu, 2))) = matriu
