英文:
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论