Wrox Programmer Forums
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old August 24th, 2010, 04:16 PM
Authorized User
Join Date: Feb 2009
Posts: 25
Thanks: 11
Thanked 0 Times in 0 Posts
Post Help with VBA!

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
Old August 25th, 2010, 05:22 PM
Friend of Wrox
Join Date: Feb 2007
Posts: 163
Thanks: 0
Thanked 2 Times in 2 Posts

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:
  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
Old August 30th, 2010, 09:44 PM
Authorized User
Join Date: Feb 2009
Posts: 25
Thanks: 11
Thanked 0 Times in 0 Posts
Default Help with VBA!

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.
Old September 9th, 2010, 02:56 PM
Friend of Wrox
Join Date: Feb 2007
Posts: 163
Thanks: 0
Thanked 2 Times in 2 Posts

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:
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.

Similar Threads
Thread Thread Starter Forum Replies Last Post
If-Then VBA seventy9mph Excel VBA 3 April 21st, 2009 06:14 PM
VBA help davidbrooks Visual Basic 2008 Essentials 2 March 26th, 2008 07:25 PM
Code works in Excel VBA but not Access VBA fossx Access VBA 2 May 21st, 2007 08:00 AM
New to VBA. Please help me. rupen Access VBA 1 May 27th, 2005 07:54 AM
Excel VBA to SQL & back to VBA edesousa Excel VBA 1 June 1st, 2004 02:39 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.