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

 

[Fluff]

I'm surprised it even gets that far, as this line will give an error

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

because the range is not valid.

 

[Rfriend]

I'm surprised it even gets that far, as this line will give an error

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

because the range is not valid.

I just changed that to try something. The original range was B:B. Changed it back to get the original error.

 

[Fluff]

Try changing findvalue from Variant to Range.

 

[Rfriend]

Try changing findvalue from Variant to Range.

I had that originally and get Run-time error:91 Object variable or With Block variable not set.

 

[Rfriend]

Try changing findvalue from Variant to Range.

I have messed with this for days and keep getting stuck at this point. The code runs a user form that searches an employee database the returns the info to a list box. The list box is a double click event that drops the "Emp#" values into the controls. From there I can edit and delete. Everything works except the edit.

 

[dmt32]

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

 

[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

I get the 744 error "Search text not found"

 

[dmt32]

I get the 744 error "Search text not found"

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

 

[Fluff]

Is Emp1 a numerical value, or text?