I am trying to search for a name in a very long list and record the sick, vacation, and personal hours that are listed for each employee. Everytime I try to run this code I get a Run Time 1004 Application or Object not Defined error. Where am I going wrong?
I keep encountering the error on the first line of the Do Until Loop.
Sub PayrollHours()
Dim EmplName As String
Dim FindCell As Range
Dim SickCell As Range
Dim PersonalCell As Range
Dim VacationCell As Range
Dim HoursLeft As Range
Dim NameCell As Range
Dim OffRow As Integer
OffRow = 1
Worksheets("Total Hours Calculator").Range("C8, D8, E8, F8, G8").ClearContents
Set NameCell = ThisWorkbook.Worksheets("Total Hours Calculator").Range("C8")
Set SickCell = ThisWorkbook.Worksheets("Total Hours Calculator").Range("D8")
Set PersonalCell = ThisWorkbook.Worksheets("Total Hours Calculator").Range("E8")
Set VacationCell = ThisWorkbook.Worksheets("Total Hours Calculator").Range("F8")
Set HoursLeft = ThisWorkbook.Worksheets("Total Hours Calculator").Range("G8")
EmplName = InputBox("Please Enter the Employee's Name")
ThisWorkbook.Worksheets("Payroll_Detail").Range("A:A").Select
Set FindCell = Selection.Find(EmplName, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
If FindCell Is Nothing Then
MsgBox "Name Not Found"
Else: NameCell = FindCell.Value
End If
With Workbooks("payroll hour calculator").Worksheets("Payroll_Detail")
Do Until FindCell.Offset(OffRow, 0).Value = ""
If FindCell.Offset(OffRow, 0).Value = "Sick" Then
SickCell = SickCell + FindCell.Offset(OffRow, 1).Value
ElseIf FindCell.Offset(OffRow, 0).Value = "Personal" Then
PersonalCell = PersonalCell + FindCell.Offset(OffRow, 1).Value
ElseIf FindCell.Offset(OffRow, 0).Value = "Vacation" Then
VacationCell = VacationCell + FindCell.Offset(OffRow, 1).Value
End If
OffRow = OffRow + 1
Loop
End With
HoursLeft = 152 - (SickCell + PersonalCell + VacationCell)
End Sub
Bookmarks