VBA运行时间错误91使用搜索和更新命令

Question

我正在使用一个代码来通过用户形式更新数据库中的数据。它的第一部分，即搜索数据正常工作，但第二部分，即更新的某个时候工作正常，但有时会出现运行时错误91

需要帮助

Private Sub cmd_Update_Click()
Application.DisplayAlerts = False
Dim ws As Worksheet
'check for a Name number
If Trim(Me.TextBox_Search_Data.Value) = "" Then
Me.TextBox_Search_Data.SetFocus
MsgBox "Please fill the data in search box"
Exit Sub
End If
Set ws = Worksheets("Employee Data")
With ws
r.Value = Me.TextBox_Search_Data.Value
r.Offset(, 1).Value = Me.TextBox_EmployeeName.Value
r.Offset(, 2).Value = Me.TextBox_FatherHusbandName.Value
r.Offset(, 3).Value = Me.ComboBox_Designation.Value
r.Offset(, 4).Value = Me.ComboBox_Category.Value

Me.TextBox_Search_Data.SetFocus
MsgBox "Data Updated Sucessfully"
'clear the data
Me.TextBox_EmployeeNumber.Value = ""
Me.TextBox_EmployeeName.Value = ""
Me.TextBox_FatherHusbandName.Value = ""
Me.ComboBox_Designation.Value = ""
Me.ComboBox_Category.Value = ""

End With
End Sub

Answer 1

看起来表可能无法正确设置，因此无法使用对象来更新。请参阅下面的修改代码：

Private Sub cmd_Update_Click()
    Application.DisplayAlerts = False
    Dim ws As Worksheet
    'check for a Name number
    If Trim(Me.TextBox_Search_Data.Value) = "" Then
        Me.TextBox_Search_Data.SetFocus
        MsgBox "Please fill the data in search box"
        Exit Sub
    End If
    ' Change ThisWorkbook to a different workbook variable as needed.
    Set ws = ThisWorkbook.Worksheets("Employee Data")  
    If Not ws Is Nothing Then
            If not r is Nothing Then
                With r
                    .Value = Me.TextBox_Search_Data.Value
                    .Offset(, 1).Value = Me.TextBox_EmployeeName.Value
                    .Offset(, 2).Value = Me.TextBox_FatherHusbandName.Value
                    .Offset(, 3).Value = Me.ComboBox_Designation.Value
                    .Offset(, 4).Value = Me.ComboBox_Category.Value
                End With
           Else
               'This will run if r is not set to a range.
           End If
    Else
        'This will occur if the sheet isn't set properly.
    End If
    Me.TextBox_Search_Data.SetFocus
    MsgBox "Data Updated Sucessfully"
    'clear the data
    Me.TextBox_EmployeeNumber.Value = ""
    Me.TextBox_EmployeeName.Value = ""
    Me.TextBox_FatherHusbandName.Value = ""
    Me.ComboBox_Designation.Value = ""
    Me.ComboBox_Category.Value = ""
End Sub