Excel VBA:
I'm struggling with the loop structure of an iterative counter to report running totals of hours worked per employee. Ex:
Day 1: 8 hrs worked, 8 hrs total
Day 2: 8 hrs worked, 16 hrs total
Day 3: 10 hrs worked, 26 hrs total
etc.
Currently, I'm trying to figure out why this doesn't loop through each value in the hrs range
Application.Intersect(cellRow, hrsRng)
Rather, it grabs the first reported hour value and runs that through the counter. Rearranging the structure I can get the total weeks hours, but not the running total.
The fuller version tracks multiple employees across different phases of a project for pivot reporting, thus the additional "name" condition. Running daily total rather than just weekly total is important due to when federal and CA overtime laws are triggered during the workweek.
Any help is greatly appreciated!
Option Explicit
Function runTot(ByVal nm As String, nmRng As Range, dt As Range, dtRng As Range, hrsRng As Range, weekSt As String) As Double
Dim wb As Workbook
Dim ws As Worksheet
Dim cell As Range
Dim i As Integer 'iterative placeholder
Dim cnt As Integer 'counter
Dim callRng As Range 'Application.Caller row
Dim wkStart As String 'sheet list validation to vb day
Dim wkDateStart As Date 'week date start
Dim wkDateEnd As Date 'week date end
Dim dateVal As Date 'date, from Caller.Row. used to find week bracketing dates
Dim dateCell As Date 'loop date
Dim hrsVal As Range 'loop hours
Dim cellRow As Range 'cell row
Set wb = ThisWorkbook
Set ws = Sheets("Budget")
i = 0
cnt = 0
Set callRng = ws.Range(Rows(Application.Caller.Row).Address)
dateVal = Application.Intersect(callRng, dtRng).Value
Select Case weekSt
Case Is = "Sunday"
wkStart = vbSunday
Case Is = "Monday"
wkStart = vbMonday
Case Is = "Tuesday"
wkStart = vbTuesday
Case Is = "Wednesday"
wkStart = vbWednesday
Case Is = "Thursday"
wkStart = vbThursday
Case Is = "Friday"
wkStart = vbFriday
Case Is = "Saturday"
wkStart = vbSaturday
End Select
'Sets Week Date Start and Week Date End, from Caller Date and day of the week
Select Case Weekday(dateVal, wkStart)
Case 1
wkDateStart = dateVal
wkDateEnd = DateAdd("d", 6, dateVal)
Case 2
wkDateStart = DateAdd("d", -1, dateVal)
wkDateEnd = DateAdd("d", 5, dateVal)
Case 3
wkDateStart = DateAdd("d", -2, dateVal)
wkDateEnd = DateAdd("d", 4, dateVal)
Case 4
wkDateStart = DateAdd("d", -3, dateVal)
wkDateEnd = DateAdd("d", 3, dateVal)
Case 5
wkDateStart = DateAdd("d", -4, dateVal)
wkDateEnd = DateAdd("d", 2, dateVal)
Case 6
wkDateStart = DateAdd("d", -5, dateVal)
wkDateEnd = DateAdd("d", 1, dateVal)
Case 7
wkDateStart = DateAdd("d", -6, dateVal)
wkDateEnd = dateVal
End Select
For Each cell In dtRng
Set cellRow = ws.Range(Rows(cell.Row).Address)
Set hrsVal = Application.Intersect(cellRow, hrsRng)
dateCell = Application.Intersect(cellRow, dtRng).Value
Do While cnt <= (dateVal - wkDateStart)
If Application.Intersect(cellRow, nmRng) = nm Then
'Debug.Print hrsVal.Address & " " & hrsVal.Value
i = i + hrsVal.Value
runTot = i
cnt = cnt + 1
End If
Loop
Next cell
End Function
