在表中查找行并从用户窗体插入值

Question

对不起，我已经编辑了很多次，它令人困惑，所以我将重新开始。 我写了 2 个不同的函数，它们都在工作，我试图将 2 个函数结合起来以获得这个功能，但我遇到了麻烦。 我想找到一行具有匹配名称的行，然后在该行的 5 列中插入值。

现在我在这一行收到一个错误"对象不支持此属性或方法"：

.单元格(最后一行，5(。值 = 文本框 1.值

Private Sub OKButton_Click()
If ComboBox1.Value = "" Then
MsgBox "Please Select a member of staff", , "Error"
Exit Sub
End If
If CheckBox1.Value = False And CheckBox2.Value = False And CheckBox3.Value = False And CheckBox4.Value = False And CheckBox5.Value = False Then
'do nothing
Else
Dim Counter As Integer, EmployeeName As String, LastRow As Long
Call SmoothCodeStart
EmployeeName = ComboBox1.Value
With Sheets("Timetable").ListObjects("TblTimetable")
LastRow = .range.Rows.Count
For Counter = LastRow To 1 Step -1
If .DataBodyRange.Cells(Counter, .ListColumns("Name and Surname").Index) = EmployeeName Then
.Cells(LastRow, 4).Value = TextBox1.Value
.Cells(LastRow, 5).Value = TextBox2.Value
.Cells(LastRow, 6).Value = TextBox3.Value
.Cells(LastRow, 7).Value = TextBox4.Value
.Cells(LastRow, 8).Value = TextBox5.Value
End If
Next Counter
End With
End If
Call SmoothCodeEnd
Unload Me
End Sub

对，我从头开始，这段代码找到正确的行并将其删除，现在删除它，我希望它将文本框 1：5 中的值添加到 4：8 列

Private Sub OKButton_Click()
If Me.ComboBox1.Value = "" Then
MsgBox "Please Select a member of staff", , "Error"
Exit Sub
End If
If CheckBox1.Value = False And CheckBox2.Value = False And CheckBox3.Value = False And CheckBox4.Value = False And CheckBox5.Value = False Then
'do nothing
Else
Dim Counter As Integer, EmployeeName As String, LastRow As Long
EmployeeName = ComboBox1.Value
With Sheets("Timetable").ListObjects("TblTimetable")
LastRow = .range.Rows.Count
For Counter = LastRow To 1 Step -1
If .DataBodyRange.Cells(Counter, .ListColumns("Name and Surname").Index) = EmployeeName Then
.ListRows(Counter).Delete
End If
Next Counter
End With
End If
Unload Me
End Sub

我试过而不是：

.Cells(LastRow, 4).Value = TextBox1.Value

改用这个：

.Cells(Counter, 4).Value = TextBox1.Value

我试过这个：

With .ListRows(Counter)
.Columns(, 4).Value = TextBox1.Value
End With

新的工作代码非常感谢威廉姆斯@tim它的工作，但它可以工作，但是在用户表单关闭之前我按下确定按钮后有 3 秒的暂停，有人有任何想法吗？

Private Sub OKButton_Click()
If Me.ComboBox1.Value = "" Then
MsgBox "Please Select a member of staff", , "Error"
Exit Sub
End If
If CheckBox1.Value = False And CheckBox2.Value = False And CheckBox3.Value = False And CheckBox4.Value = False And CheckBox5.Value = False Then
'do nothing
Else
Dim EmployeeName As String, f As range, tbl As ListObject, Counter As Integer, LastRow As Long, listcolumns As range
EmployeeName = ComboBox1.Value
With Sheets("Timetable").ListObjects("TblTimetable")
LastRow = .range.Rows.Count
For Counter = LastRow To 1 Step -1
Set f = .DataBodyRange.Cells(Counter, .listcolumns("Name and Surname").Index)
If f = EmployeeName Then
With f.EntireRow
.Cells(5).Value = TextBox1.Value
.Cells(6).Value = TextBox2.Value
.Cells(7).Value = TextBox3.Value
.Cells(8).Value = TextBox4.Value
.Cells(9).Value = TextBox5.Value
End With
End If
Next Counter
End With
End If
Unload Me
End Sub

Answer 1

以下是您可以使用的方法的概述：

Dim EmployeeName As String, f As Range, tbl As ListObject
EmployeeName = "Joe Brown"
Set tbl = Sheets("Timetable").ListObjects("TblTimetable")
'find the row
Set f = tbl.ListColumns("Name and Surname").DataBodyRange.Find( _
what:=EmployeeName, lookat:=xlWhole)
'if found a row, update it
If Not f Is Nothing Then
With f.EntireRow
.Cells(4).Value = "Value_1"
.Cells(5).Value = "Value_2"
'etc etc
End With
End If

Answer 2

感谢威廉姆斯@tim帮助我获得此功能

Private Sub OKButton_Click()
If Me.ComboBox1.Value = "" Then
MsgBox "Please Select a member of staff", , "Error"
Exit Sub
End If
If CheckBox1.Value = False And CheckBox2.Value = False And CheckBox3.Value = False And CheckBox4.Value = False And CheckBox5.Value = False Then
'do nothing
Else
Dim EmployeeName As String, f As range, tbl As ListObject, Counter As Integer, LastRow As Long, listcolumns As range
EmployeeName = ComboBox1.Value
With Sheets("Timetable").ListObjects("TblTimetable")
LastRow = .range.Rows.Count
For Counter = LastRow To 1 Step -1
Set f = .DataBodyRange.Cells(Counter, .listcolumns("Name and Surname").Index)
If f = EmployeeName Then
With f.EntireRow
.Cells(5).Value = TextBox1.Value
.Cells(6).Value = TextBox2.Value
.Cells(7).Value = TextBox3.Value
.Cells(8).Value = TextBox4.Value
.Cells(9).Value = TextBox5.Value
End With
End If
Next Counter
End With
End If
Unload Me
End Sub