添加新行到Excel表（VBA） [英] Add new row to excel Table (VBA)

问题描述

我有一个excel，用来记录你吃的特定日子和饭菜的食物。我有一个网格，每条线代表你吃的食物，多少糖等等。

然后我添加了一个保存按钮来保存所有的数据

这是我尝试过的

  Public Sub addDataToTable（ByVal strTableName As String，ByRef arrData As Variant）
 Dim lLastRow As Long 
 Dim iHeader As Integer 
 Dim iCount As Integer 
 
 With Worksheets （4）.ListObjects（strTableName）
'找到列表的最后一行
 lLastRow = Worksheets（4）.ListObjects（strTableName）.ListRows.Count 
 
'从一个额外的行，如果列表有头
如果.Sort.Header = xlYes然后
 iHeader = 1 
 Else 
 iHeader = 0 
结束如果
结束与
 
'循环数组以添加每个值
对于iCount = LBound（arrData）到UBound（arrData）
 **工作表（4）.Cells（lLastRow + 1 ，iCount）.Value = arrData（iCount）** 
下一个iCount 
 End Sub 
  

 应用程序定义或对象定义的错误
  

我做错了什么？

提前感谢！

解决方案

你不会说你正在使用哪个版本的Excel。这是为2007/2010编写的（Excel 2003需要一个不同的apprach）

你也不会说你如何调用 addDataToTable 和你传递到 arrData 。

我猜你正在传递一个 0 数组。如果是这种情况（并且表从列 A 中开始），那么 iCount 将从 0 和 .Cells（lLastRow + 1，iCount）将尝试引用列 0 这是无效的。

您还没有利用 ListObject 。您的代码假定 ListObject 1位于行 1 之间。如果不是这样，你的代码将数据放在错误的行中。

这里有一个替代方法，使用 ListObject

  Sub MyAdd（ByVal strTableName As String，ByRef arrData As Variant）
 Dim Tbl As ListObject 
 Dim NewRow As ListRow 
 
'基于OP 
'Set Tbl = Worksheets（4）.ListObjects（strTableName）
'或者更好的是，在工作簿中的任何工作表上获取
 Set Tbl = Range（strTableName）.ListObject 
 Set NewRow = Tbl.ListRows.Add（AlwaysInsert：= True）
 
'处理数组和范围
如果TypeName arrData）=Range然后
 NewRow.Range = arrData.Value 
 Else 
 NewRow.Range = arrData 
 End If 
 End Sub 
  

可以通过多种方式调用：

  Sub zx（）
'传递从范围
复制的变体数组MyAddMyTable，[G1：J1] .Value 
'Pass一个范围
 MyAddMyTable，[G1：J1] 
'传递数组
 MyAddMyTable，Array（1，2，3，4）
 End Sub 
  




I have an excel which serves to record the food you ingest for a specific day and meal. I hav a grid in which each line represent a food you ate, how much sugar it has, etc.

Then i've added an save button to save all the data to a table in another sheet.

This is what i have tried

    Public Sub addDataToTable(ByVal strTableName As String, ByRef arrData As Variant)
    Dim lLastRow As Long
    Dim iHeader As Integer
    Dim iCount As Integer

    With Worksheets(4).ListObjects(strTableName)
        'find the last row of the list
        lLastRow = Worksheets(4).ListObjects(strTableName).ListRows.Count

        'shift from an extra row if list has header
        If .Sort.Header = xlYes Then
            iHeader = 1
        Else
            iHeader = 0
        End If
    End With

    'Cycle the array to add each value
    For iCount = LBound(arrData) To UBound(arrData)
        **Worksheets(4).Cells(lLastRow + 1, iCount).Value = arrData(iCount)**
    Next iCount
End Sub

but i keep getting the same error on the highlighted line:

Application-defined or object-defined error

What i am doing wrong?

Thanks in advance!

解决方案

You don't say which version of Excel you are using. This is written for 2007/2010 (a different apprach is required for Excel 2003 )

You also don't say how you are calling addDataToTable and what you are passing into arrData.
I'm guessing you are passing a 0 based array. If this is the case (and the Table starts in Column A) then iCount will count from 0 and .Cells(lLastRow + 1, iCount) will try to reference column 0 which is invalid.

You are also not taking advantage of the ListObject. Your code assumes the ListObject1 is located starting at row 1. If this is not the case your code will place the data in the wrong row.

Here's an alternative that utilised the ListObject

Sub MyAdd(ByVal strTableName As String, ByRef arrData As Variant)
    Dim Tbl As ListObject
    Dim NewRow As ListRow

    ' Based on OP 
    ' Set Tbl = Worksheets(4).ListObjects(strTableName)
    ' Or better, get list on any sheet in workbook
    Set Tbl = Range(strTableName).ListObject
    Set NewRow = Tbl.ListRows.Add(AlwaysInsert:=True)

    ' Handle Arrays and Ranges
    If TypeName(arrData) = "Range" Then
        NewRow.Range = arrData.Value
    Else
        NewRow.Range = arrData
    End If
End Sub

Can be called in a variety of ways:

Sub zx()
    ' Pass a variant array copied from a range
    MyAdd "MyTable", [G1:J1].Value
    ' Pass a range
    MyAdd "MyTable", [G1:J1]
    ' Pass an array
    MyAdd "MyTable", Array(1, 2, 3, 4)
End Sub

这篇关于添加新行到Excel表（VBA）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！