Help with VBA!

billy1r · August 24th, 2010, 04:16 PM

I have a work sheet (T-EMP) which keeps everyone's hours for two weeks. The hours worked cells default to 8. Two rows above the hours worked cells are cells where the hours you actually worked go(From - to), ie. 6a - 2p, 2p - 10p, 10p - 6a. Cell U4 is where the shift goes ( 1st Watch, 2nd Watch, 3rd Watch). What I am trying to do is use VBA to fill the Time Worked cells with the appropriate hours according to what is in cell U4, but only if the hours worked cell two rows below contains "8". If there is a "O" (for Off) in the hours worked cell, then I want to leave the Time Worked cell blank.
The workbook is made with a setup sheet where you tell it what location, what shift and which class of employees, then it goes and gets the rosters and makes new sheets to hold the names with the information

allenm · August 25th, 2010, 05:22 PM

Are there 2 cells that have a time in them? you could just make this a simple formula. A1 is time in, B1 is time out. This is my C1 formula.

=If(U4="O","",INT(B1-A1) * 24 + HOUR(B1-A1) + MINUTE(B1-A1)/60)

This can also be done with code. Modify this to suit your needs:

Code:

  Dim wsThisSheet As Worksheet, dDifDate As Date, fTimeWorked As Double
  Set wsThisSheet = ActiveWorkbook.ActiveSheet
  With wsThisSheet
'Only process valid dates when U4 is not an upper or lower case O
    If IsDate(.Range("A1").Value) And IsDate(.Range("B1").Value) And UCase(.Range("U4").Value) <> "O" Then

'This allows time in and time out to be reversed yet still calculate right
      If .Range("B1") < .Range("A1") _
        Then dDifDate = .Range("A1").Value - .Range("B1").Value _
        Else dDifDate = .Range("B1").Value - .Range("A1").Value

      .Range("C1").Value = Int(dDifDate) * 24 + Hour(dDifDate) + Minute(dDifDate) / 60
    End If
  End With

billy1r · August 30th, 2010, 09:44 PM

I am sorry for taking so long allenm. There is one row of cells with the amount of hours worked in them (5,6,8,etc). Two rows above that is another row where I want to put 6a - 2p if U4 says 1st Watch, 2p - 10p if U4 says 2nd Watch, or 10p - 6a if U4 says 3rd Watch.
If the row with hours worked in it says "O" for Off day, then I want the cell two rows above to be left blank. Is that a better explanation?
I am not good with this posting stuff.

allenm · September 9th, 2010, 02:56 PM

You need a Worksheet_Change function on that worksheet if you want it to update itself when the cell contents change.

IF I'm understanding right then your code should be something like this:

Code:

Private Sub Worksheet_Change(ByVal Target As Range)

'When Cell U4 changes change cell T1 to read appropriately for shift
  Dim sHours As String, sRng As String
  sRng = Target.Address(False, False)
  If sRng <> "U4" And sRng <> "T3" Then Exit Sub 'exits if U4 or T3 aren't changing
  If UCase(Me.Range("T3").Value) <> "O" Then
    Select Case Trim(Me.Range("U4").Value)
      Case "1st Watch"
        sHours = "6a - 2p"
      Case "2nd Watch"
        sHours = "2p - 10p"
      Case "3rd Watch"
        sHours = "10p - 6a"
      Case Else
        sHours = ""
    End Select
  End If
  Me.Range("T1").Value = sHours

End Sub

This assumes hours worked is T3 and the value to populate 2 rows up would be T1.

Hope this helps.