Return a find

[Naitnoum]

Good day,

I'm currently having an issue with a macro I am trying to run. Essentially I want the macro to check on the status of a file in a data base. When I want to check a file out of the data base (much like a library) I want the macro to look for the specific file number and check to see if the file is "in" or if it even exists. If it does exist and is "in" I want the macro to replace the "in" with "out".

So far my code looks like so:

Private Sub CommandButton3_Click() 'Check out a client file
Dim CheckOut As Range
Dim CheckAgent As String
 
        If Workbooks("File Managment System.xls").Sheets(1).Range("E9") = "" Then
            MsgBox "Please enter a PRI."
            Exit Sub
        End If
        Workbooks.Open ("H:\FileDatabase.xls")
        Set CheckOut = Workbooks("FileDatabase.xls").Sheets(1).Range("B:B").Find(What:=Workbooks("File Managment System.xls").Sheets(1).Range("E9"), LookIn:=xlValues, Lookat:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True)
 
        If CheckOut = "In" Then
            Workbooks("File Managment System.xls").Sheets(1).Range("D" & Found.Row).Value = "Out"
            MsgBox "File Checked Out. Please take it from the cabinet."
            If CheckOut = "Out" Then
                Set CheckAgent = Workbooks("FileDatabase.xls").Sheets(1).Range("E" & Found.Row).Value
                MsgBox "File is already Checked out by" & CheckAgent
        Else
            MsgBox "File does not exist."
        End If
        End If
    Workbooks("FileDataBase.xls").Activate
    ActiveWorkbook.Save
    ActiveWorkbook.Close
End Sub

My code bugs out at the line below and states "Object Required":

Set CheckAgent = Workbooks("DIFileDatabase.xls").Sheets(1).Range("E" & Found.Row).Value

The macro should refer to what was entered in cell E9 from "File Managment System.xls" and find the matching entry from E9 in Column 'B' of "FileDatabase.xls". Once found, the macro should then review the entry in column 'D' of the row that contains the same entry as E9 (variable is named CheckOut). If the value in column 'D' is "In" then the macro will replace it with "Out". If the Value is "Out" the macro should return a message box that states the file is already out and who checked the file out last. This person would be defined by variable CheckAgent. This is where my problem lies. What am I doing wrong for my Set CheckAgent to not work? It seems to me like my syntax is fine, but I could be out to lunch on something.

Thank you.

 

[Gary McMaster]

You've declared "CheckAgent" as a string in your Dim statements. Now you are trying to "Set" it equal to a range object.

Try getting rid of "Set" in the offending statement.

Gary

 

[Naitnoum]

Thank you Gary. I knew I was overlooking something. This fixed my bug and allowed me to realize other mistakes that I was making. I have fixed those mistakes and everything runs great now.

Thanks!