Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Aug 2001
    Location
    Bloomington, Indiana, USA
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Changes to payroll system driving me crazy (2003 SP2)

    The timeclock system I use at work recently went through an upgrade that has messed up my macro that runs off of its reports. The old system would report an ending time of 12:00:00 AM if an employee forgot to clock out after their shift. I had the following piece of code in my macro that would pull out those rows into a separate worksheet in my workbook.

    Const strBegTmCol = "H"
    Const strEndTmCol = "J"
    Const strShiftHrCol = "K"

    n = wshSource.Range(strFilledCol & 65536).End(xlUp).Row
    j = wshTarget.Range(strFilledCol & 65536).End(xlUp).Row
    For i = n To 2 Step -1
    If wshSource.Range(strEndTmCol & i) = "0" Then
    j = j + 1
    With wshSource.Rows(i)
    .Copy Destination:=wshTarget.Rows(j)
    End With
    End If
    Next i

    The new data is coming back with a time of either 11:59:00 PM or 11:59:59 PM for a person that forgot to clock out. How can I modify the code to have it recognize that either of these entries in the cell should be pulled out to the new worksheet? It also looks like the data is coming in as text instead of a time, but I am not sure.

    I have attached a small sterilized version of the data to show the fields.

    Thanks in advance
    Greg <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Changes to payroll system driving me crazy (2003 SP2)

    Change the line

    If wshSource.Range(strEndTmCol & i) = "0" Then


    to

    If wshSource.Range(strEndTmCol & i) = TimeSerial(23, 59, 0) Or _
    wshSource.Range(strEndTmCol & i) = TimeSerial(23, 59, 59) Then

  3. #3
    Star Lounger
    Join Date
    Aug 2001
    Location
    Bloomington, Indiana, USA
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changes to payroll system driving me crazy (2003 SP2)

    Thank you! That was perfect. I always appreciate the rapid replies I receive from the Lounge!
    Greg <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

Posting Permissions

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