Error 424: Object Required using Find

[Rfriend]

Hello,

I am trying to run a find loop and keep getting an object required error. I cannot determine why I am getting this error. This code ran great previously. Any ideas?

Private Sub cmdEdit2_Click()
'   Page 1
    Dim findvalue As Variant
    Dim cNum As Integer
    Dim Staff_DataSH As Worksheet
'error handling
        On Error GoTo errHandler:
'hold in memory and stop screen flicker
    Application.ScreenUpdating = False
        Set Staff_DataSH = Sheet7
'check for values
    If Emp1.Value = "" Or Emp2.Value = "" Then
        MsgBox "There is nothing to edit"
        Exit Sub
    End If
'clear the listbox
    lstEmployee.RowSource = ""
'find the row to edit
    Set findvalue = Staff_DataSH.Range("B9:B").Find(What:=Me.Emp1.Value, LookIn:=xlValues, LookAt:=xlWhole)
'update the values
 With findvalue
    findvalue = Emp1.Value
    findvalue.Offset(0, 1) = Emp2.Value          ' this is where I error.  Emp1 is a unique ID#, Emp2 is the employee active status.
    findvalue.Offset(0, 2) = Emp3.Value
    findvalue.Offset(0, 5) = Emp4.Value
    findvalue.Offset(0, 6) = Emp5.Value
    findvalue.Offset(0, 3) = Emp6.Value
    findvalue.Offset(0, 4) = Emp7.Value
    findvalue.Offset(0, 15) = Emp8.Value
    findvalue.Offset(0, 8) = Emp9.Value
    findvalue.Offset(0, 10) = Emp10.Value
    findvalue.Offset(0, 17) = Emp11.Value
    findvalue.Offset(0, 18) = Emp12.Value
    findvalue.Offset(0, 19) = Emp13.Value
    findvalue.Offset(0, 20) = Emp14.Value
    findvalue.Offset(0, 21) = Emp15.Value
    findvalue.Offset(0, 22) = Emp16.Value
    findvalue.Offset(0, 11) = Emp17.Value
    findvalue.Offset(0, 23) = Emp18.Value
    findvalue.Offset(0, 24) = Emp19.Value
    findvalue.Offset(0, 25) = Emp20.Value
    findvalue.Offset(0, 26) = Emp21.Value
    findvalue.Offset(0, 27) = Emp22.Value
    findvalue.Offset(0, 28) = Emp23.Value
    findvalue.Offset(0, 29) = Emp24.Value
    findvalue.Offset(0, 30) = Emp25.Value
    findvalue.Offset(0, 31) = Emp26.Value
            With findvalue
                .Offset(0, 12) = Format(Me.Emp27.Value, "mm/dd/yyyy")
                .Offset(0, 13) = Format(Me.Emp28.Value, "mm/dd/yyyy")
            End With
    findvalue.Offset(0, 14) = Emp29.Value
    findvalue.Offset(0, 7) = Emp30.Value
    findvalue.Offset(0, 16) = Emp31.Value
 End With
    AdvFilterOutdata
    If Staff_DataSH.Range("AP9").Value = "" Then
       lstEmployee.RowSource = ""
    Else
        lstEmployee.RowSource = Staff_DataSH.Range("Outdata").Address(external:=True)
    End If
    On Error GoTo 0
          Exit Sub
errHandler:
       MsgBox "An Error has Occurred " & vbCrLf & _
       "The error number is: " & Err.Number & vbCrLf & _
        Err.Description & vbCrLf & "Please notify the administrator"
End Sub

 

[Rfriend]

You should have a test after Range.Find to ensure search value has been found

try adding line in BOLD & see if problem still persists

'find the row to edit
    Set findvalue = Staff_DataSH.Range("B:B").Find(What:=Me.Emp1.Value, LookIn:=xlValues, LookAt:=xlWhole)
    If findvalue Is Nothing Then Err.Raise 744

Dave

Then this probably answers the question to your original issue - Run-time error:91 Object variable or With Block variable not set.

Try searching for a value that you know exists & see what happens

Dave

My cmdSearch returns the value(s )I am looking for. When the line is "double clicked" the data fills all the controls correctly. When I Debug and hover over the findvalues i.e Emp1, 2, etc. the correct values are values showing. It is when I add training or edit any item in the controls I get the error. I may just be missing your point.

 

[Rfriend]

Is Emp1 a numerical value, or text?

Numeric

 

[Fluff]

In that case use

Set findvalue = Staff_DataSH.Range("B9:B").Find(What:=Val(Me.Emp1.Value), LookIn:=xlValues, LookAt:=xlWhole)

otherwise it's looking for a text value.

 

[Rfriend]

Is Emp1 a numerical value, or text?

Set findvalue = Staff_DataSH.Range("B9:B").Find(What:=Val(Me.Emp1.Value), LookIn:=xlValues, LookAt:=xlWhole)

Wow, that did it. Thank you, Thank you, Thank you!!!

 

[Fluff]

Glad we could help & thanks for the feedback.