+ Reply to Thread
Results 1 to 4 of 4

Run Time Error 1004 and I can't Find the Issue

Hybrid View

  1. #1
    Registered User
    Join Date
    06-07-2013
    Location
    New Orleans
    MS-Off Ver
    Excel 2010
    Posts
    2

    Run Time Error 1004 and I can't Find the Issue

    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
    Last edited by tracymickeyd; 06-07-2013 at 01:39 PM. Reason: Use code tags in future.

  2. #2
    Forum Expert
    Join Date
    01-12-2007
    Location
    New Jersey
    Posts
    2,127

    Re: Reteaching myself VBA and the Run Time errors are making this impossible!

    Just at a quick glance, I don't think that .Range("xRow,1") is what you are looking for here. Think about that without the variable. If xRow = 1 for example, that would be the equivalent of saying Range("1,1").

    I'm not sure exactly what the code is looking to do, but you may need a bit of code more akin to .Cells(xrow, 1) (Note: no "quotation marks")

  3. #3
    Registered User
    Join Date
    06-07-2013
    Location
    New Orleans
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Reteaching myself VBA and the Run Time errors are making this impossible!

    Ok. So I have changed the cell locations to use Offset instead so that OffRow is an integer that offsets the cell the loop looks in by 1. I also changed the .find function to .match. I'm still receiving the same error though.
    Last edited by tracymickeyd; 06-07-2013 at 01:46 PM.

  4. #4
    Forum Expert
    Join Date
    01-12-2007
    Location
    New Jersey
    Posts
    2,127

    Re: Run Time Error 1004 and I can't Find the Issue

    Any chance you can post a scrubbed down version of the file for us to work with (remove any confidential data)?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1