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

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

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

问题

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

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

  1. Private Sub but_spl2addweld_Click()
  3.     Dim sh As Worksheet
  4.     Set sh = ThisWorkbook.Sheets("PL2_steel")
  5.     
  6.     Dim tbl As ListObject
  7.     Set tbl = ThisWorkbook.Sheets("PL2_steel").ListObjects("Tbl_spl2weldings")
  9.     Dim y As Long
  10.     Dim Ir As Long
  12.     Ir = sh.ListObjects("Tbl_spl2weldings").Range.Columns(1).Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row 
  14.     With tbl
  15.         If Me.Combo_SPL2weldoptions.Value = "Option 1_ Base material-Filler material" Then
  16.             .DataBodyRange(Ir + 1, 1).Value = "pl2.St." & Me.txt_SPL2code.Value
  17.             .DataBodyRange(Ir + 1, 2).Value = Me.Txt_spl2weldid.Value
  18.             .DataBodyRange(Ir + 1, 3).Value = Me.Combo_SPL2weldoptions.Value
  19.         End If
  20.     End With
  21.    
  22. 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.

  1. Private Sub but_spl2addweld_Click()
  3.     Dim sh As Worksheet
  4.     Set sh = ThisWorkbook.Sheets("PL2_steel")
  5.     
  6.     Dim tbl As ListObject
  7.     Set tbl = ThisWorkbook.Sheets("PL2_steel").ListObjects("Tbl_spl2weldings")
  9.     Dim y As Long
  10.     Dim Ir As Long
  12.     Ir = sh.ListObjects("Tbl_spl2weldings").Range.Columns(1).Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row 
  14.        With tbl
  15.         If Me.Combo_SPL2weldoptions.Value = "Option 1_ Base material-Filler material" Then
  16.          .DataBodyRange(Ir + 1, 1).Value = "pl2.St." & Me.txt_SPL2code.Value
  17.          .DataBodyRange(Ir + 1, 2).Value = Me.Txt_spl2weldid.Value
  18.          .DataBodyRange(Ir + 1, 3).Value = Me.Combo_SPL2weldoptions.Value
  20.         End If
  21.        End With
  22.        
  23.  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

以下是翻译好的部分：

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

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

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

  1. Private Function FirstEmptyColumnRow(table As ListObject, columnNumber As Long) As Long
  3.     ' 目的：
  4.     ' 返回表格列中第一个空单元格的行号
  5.     
  6.     ' 将表格数据存储为数组
  7.     Dim tableData() As Variant
  8.     tableData = table.DataBodyRange.Value
  9.     
  10.     ' 遍历行
  11.     Dim Row As Long
  12.     For Row = 1 To UBound(tableData, 1)
  13.     
  14.         ' 检查此行列中的数据是否为空
  15.         If IsEmpty(tableData(Row, columnNumber)) Then
  16.         
  17.             ' 如果数据为空，返回行号并退出函数
  18.             FirstEmptyColumnRow = Row
  19.             Exit Function
  20.             
  21.         End If
  22.     
  23.     Next Row
  24.     
  25.     ' 如果没有空行，则将值设置为行数
  26.     FirstEmptyColumnRow = Row
  28. End Function

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

  1. Dim lr As Long
  2. 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:

  1. Private Function FirstEmptyColumnRow(table As ListObject, columnNumber As Long) As Long
  3.     ' Purpose:
  4.     ' Return the row number of the first empty cell in the table's column
  5.     
  6.     ' Store the table data as an array
  7.     Dim tableData() As Variant
  8.     tableData = table.DataBodyRange.Value
  9.     
  10.     ' Iterate through the rows
  11.     Dim Row As Long
  12.     For Row = 1 To UBound(tableData, 1)
  13.     
  14.         ' Check if the data in the column of this row is Empty
  15.         If IsEmpty(tableData(Row, columnNumber)) Then
  16.         
  17.             ' If the data is Empty, return the Row number and exit the function
  18.             FirstEmptyColumnRow = Row
  19.             Exit Function
  20.             
  21.         End If
  22.     
  23.     Next Row
  24.     
  25.     ' If there were no empty rows, then set the value to the number of rows
  26.     FirstEmptyColumnRow = Row
  28. 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:

  1. Dim lr as Long
  2. 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
  • excel
  • row
  • vba
匿名

发表评论

匿名网友

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

确定