功能或子添加新的行和数据到表 [英] Function or sub to add new row and data to table

问题描述

我想创建一个Sub，它基本上允许我使用一个特定的名称来定位一个Excel表，然后在底部插入一个新的行，并在同一时间向该行添加数据。然后退出子。如果表只有一行没有数据，请将数据添加到该行，然后退出子。

I want to create a Sub that basically allows me to target an Excel table with a specific name and then insert a new row at the bottom and add data to that row at the same time. Then exit the sub. And if the table only has one row with no data in it, add the data to that row and then exit the sub.

我该怎么做？

在伪代码中我正在想这样的事情：

I was thinking something like this, in pseudo code:

Public Sub addDataToTable(ByVal strTableName as string, ByVal strData as string, ByVal col as integer)

ActiveSheet.Table(strTableName).Select
If strTableName.Rows.Count = 1 Then
    strTableName(row, col).Value = strData
Else
    strTable(lastRow, col).Value = strData
End if

End Sub

这可能是作为代码无效，但它至少应该解释我的后面！

This is probably not valid as code at all, but it should explain what I'm after at least!

推荐答案

这是你正在看的对于？

Option Explicit

Public Sub addDataToTable(ByVal strTableName As String, ByVal strData As String, ByVal col As Integer)
    Dim lLastRow As Long
    Dim iHeader As Integer

    With ActiveSheet.ListObjects(strTableName)
        'find the last row of the list
        lLastRow = ActiveSheet.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
    'add the data a row after the end of the list
    ActiveSheet.Cells(lLastRow + 1 + iHeader, col).Value = strData
End Sub

它处理这两种情况是否有头或不。

It handles both cases whether you have header or not.

这篇关于功能或子添加新的行和数据到表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！