在查找表中第一个可用的空行的方法不起作用。

huangapple go评论64阅读模式
英文:

Methods for finding first available empty row in table in not working

问题

我通常不太喜欢使用表格,但这次由于许多与工作相关的原因,我必须使用表格。因此,我现在正在将范围代码转换为修改后的表格代码,并且我每天都会遇到很多问题。

这个问题让我感到烦恼。这是一个用于查找表格列的最后一行并将数据插入到下一个空行的代码,以便接收数据的代码。

Private Sub but_spl2addweld_Click()

    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets("PL2_steel")
    
    Dim tbl As ListObject
    Set tbl = ThisWorkbook.Sheets("PL2_steel").ListObjects("Tbl_spl2weldings")

    Dim y As Long
    Dim Ir As Long

    Ir = sh.ListObjects("Tbl_spl2weldings").Range.Columns(1).Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row 

    With tbl
        If Me.Combo_SPL2weldoptions.Value = "Option 1_ Base material-Filler material" Then
            .DataBodyRange(Ir + 1, 1).Value = "pl2.St." & Me.txt_SPL2code.Value
            .DataBodyRange(Ir + 1, 2).Value = Me.Txt_spl2weldid.Value
            .DataBodyRange(Ir + 1, 3).Value = Me.Combo_SPL2weldoptions.Value
        End If
    End With
   
End Sub

我尝试了许多不同的方法来查找列(1)的最后一行 'Ir',并将我的新数据放在下一个空行 'Ir+1' 中,就像在范围系统中一样。但是没有一种方法能按照我想要的方式工作。例如,当前的代码总是将新数据放在标题后的第二行,而不管列1是满的还是空的。

英文:

I normally hesitate to use tables but this time is for many work related reasons and I must use tables. Therefore now I am in the process of converting the range codes to table modified codes and Im having lots of problems which I figure out day by day.
This one but is annoying me. This is a code to find the last full row of a table column and insert the data from my userform textboxes to the next row which is empty to take in data.

Private Sub but_spl2addweld_Click()

    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets("PL2_steel")
    
    Dim tbl As ListObject
    Set tbl = ThisWorkbook.Sheets("PL2_steel").ListObjects("Tbl_spl2weldings")

    Dim y As Long
    Dim Ir As Long

    Ir = sh.ListObjects("Tbl_spl2weldings").Range.Columns(1).Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row 

       With tbl
        If Me.Combo_SPL2weldoptions.Value = "Option 1_ Base material-Filler material" Then
         .DataBodyRange(Ir + 1, 1).Value = "pl2.St." & Me.txt_SPL2code.Value
         .DataBodyRange(Ir + 1, 2).Value = Me.Txt_spl2weldid.Value
         .DataBodyRange(Ir + 1, 3).Value = Me.Combo_SPL2weldoptions.Value

        End If
       End With
       
 End Sub

I tried many different methods to find the last full row of column(1) 'Ir' and put my new data in the next row which is empty 'Ir+1' like in the range system. But nothing works as I want. For example the current code, puts the new data always in the same row which is the second row after the header, regardless of column 1 being full or empty.

答案1

得分: 1

以下是翻译好的部分:

你想要一种方法来获取表格列中的第一行为空的行。要做到这一点:

  • 遍历每一行
  • 检查特定的列是否有数据
  • 如果有数据,则继续搜索
  • 如果没有数据,我们就找到了第一个空行

这里有一个执行此操作的函数:

Private Function FirstEmptyColumnRow(table As ListObject, columnNumber As Long) As Long

    ' 目的:
    ' 返回表格列中第一个空单元格的行号
    
    ' 将表格数据存储为数组
    Dim tableData() As Variant
    tableData = table.DataBodyRange.Value
    
    ' 遍历行
    Dim Row As Long
    For Row = 1 To UBound(tableData, 1)
    
        ' 检查此行列中的数据是否为空
        If IsEmpty(tableData(Row, columnNumber)) Then
        
            ' 如果数据为空,返回行号并退出函数
            FirstEmptyColumnRow = Row
            Exit Function
            
        End If
    
    Next Row
    
    ' 如果没有空行,则将值设置为行数
    FirstEmptyColumnRow = Row

End Function

由于这是一个函数,您可以用它来处理任何表格和列号。在您的情况下,可以这样使用:

Dim lr As Long
lr = FirstEmptyColumnRow(tbl, 1)

在可能的情况下,尽量让您的代码只完成一个简单的任务。这段代码只是查找空行,不会添加新行或插入新数据。现在,您可以创建一个子程序,使用这个行号,并以您想要的方式添加数据。

最后,我强烈建议学习如何将表格存储为数组,因为在VBA中,与直接操作表格相比,使用数组要快得多。

英文:

You want a way to get the first row that is empty in a table's column. To do this:

  • Iterate through each row
  • Check a specific column to see if there is data
  • If there is data, continue searching
  • If there is not data, we have found the first empty row

Here's a function that does this:

Private Function FirstEmptyColumnRow(table As ListObject, columnNumber As Long) As Long

    ' Purpose:
    ' Return the row number of the first empty cell in the table's column
    
    ' Store the table data as an array
    Dim tableData() As Variant
    tableData = table.DataBodyRange.Value
    
    ' Iterate through the rows
    Dim Row As Long
    For Row = 1 To UBound(tableData, 1)
    
        ' Check if the data in the column of this row is Empty
        If IsEmpty(tableData(Row, columnNumber)) Then
        
            ' If the data is Empty, return the Row number and exit the function
            FirstEmptyColumnRow = Row
            Exit Function
            
        End If
    
    Next Row
    
    ' If there were no empty rows, then set the value to the number of rows
    FirstEmptyColumnRow = Row

End Function

Since this is written as a function, you can use it for whatever tables and column numbers you want. In your case, something like this:

Dim lr as Long
lr = FirstEmptyColumnRow(tbl, 1)

Whenever possible, try to keep your code doing one simple job. See, this code only finds the row that is empty. It doesn't add a new row, or insert new data. Now, I would create a sub to take this row number, and add the data in the way that you want.

Finally, I highly recommend learning how to store tables as arrays, because they are much faster to work with in VBA than working with the tables directly.

huangapple
  • 本文由 发表于 2023年7月27日 21:22:10
  • 转载请务必保留本文链接:https://go.coder-hub.com/76780194.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定