Function Help

[ghosttech09]

Hi and thanks in advance to whom ever helps. I have to functions to add work hours as a 7 day total and 30 day total. Both have also a reset function as well if hours entered 2 days in a row as 0 then they both reset to 0 and on the 7 day if the hours entered as 0 on one day only the 7day total resets to 0.I have a drop down list in column C10:C375. Now My issue is whenever the dropdown lists a certain "text" I would like the 7 day total to reset regardless of the hours entered and if a 0 is entered afterwards then both the 30 day and 7 day totals reset. PLease help any further questions just ask.

[vba]Function MonthlySummary(Hrsworked) As Currency
Dim item As Variant
Dim zero As Boolean

zero = False
MonthlySummary = 0
For Each item In Hrsworked
If (item = "") Then item = 0
If ((zero = True) And (item = 0#)) Then MonthlySummary = 0# Else _
MonthlySummary = MonthlySummary + item
zero = False
If (item = 0#) Then zero = True
Next item

End Function[/vba]

[vba]Function Workhrs(Hrsworked) As Currency
Dim item As Variant

Workhrs = 0#
For Each item In Hrsworked
If (item = "") Then Workhrs = 0#
If (item = 0#) Then Workhrs = 0# Else _
Workhrs = Workhrs + item
Next item

End Function
[/vba]



Above are the 2 Modules that are my functions as of right now.

[Bob Phillips]

Can you post a workbook example, and details of what to input and what you want to see?

[ghosttech09]

Example post.xlsm Yes here it is. What i need to see is whenever the medevac duty column reads "1st OFF" I need to have the 7 day total reset to 0 and the 30 day total stay the same unless followed by a 0 as the input then both reset.

[Bob Phillips]

Is this what you mean?

[vba]

Option Explicit

Const MEDEVAC_DUTY As String = _
"2nd UP,1ST UP,1st OFF,CQ OFF,AMR,APPT,CQ,TF,FD,Mission,EGLIN,LEAVE,PASS,GROUNDED,SCHOOL,ADMIN DAY,MEDEXPRESS"
Const MEDEVAC_CI As String = _
"6,3,1,1,46,46,32,40,30,39,13,4,4,15,53,5,5"

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit

Application.EnableEvents = False

If Not Intersect(Target, Me.Range("C10:C375")) Is Nothing Then

If Target.Value2 = "1st OFF" Then

Target.Offset(0, 2).Value2 = 0
If Target.Offset(1, 3).Value2 = 0 Then

Target.Offset(0, 3).Value2 = 0
End If
End If
End If

ws_exit:
Application.EnableEvents = True
End Sub

Sub worksheet_SelectionChange(ByVal Target As Range)
Dim oCell As Range
Dim vecMedeVac As Variant
Dim vecMedeVacCI As Variant
Dim idxMedevac As Long
Dim zero As Boolean

If Not Intersect(Target, Me.Range("C10:C375")) Is Nothing Then

vecMedeVac = Split(MEDEVAC_DUTY, ",")
vecMedeVacCI = Split(MEDEVAC_CI, ",")
For Each oCell In Target

idxMedevac = 0
On Error Resume Next
idxMedevac = Application.Match(oCell.Value, vecMedeVac, 0)
On Error GoTo 0

With oCell.Interior

If idxMedevac > 0 Then

.ColorIndex = CLng(vecMedeVacCI(idxMedevac))

Else
.ColorIndex = 2
End If

.Pattern = xlSolid
End With
Next
End If
End Sub[/vba]

[ghosttech09]

It's really close but it seems to continue on after the first off day where as it needs to completely reset it as if a 0 was entered into the input box even though it has not.

[Bob Phillips]

Sorry, can you explain that with an example?

[ghosttech09]

Example post.xlsm Sure I can send the example again with comments hopefully that gives you more of an idea of what I needed to happen.

[Bob Phillips]

One comment says ... this needs to reset due to a consecutive 48 hours off... but I only see 1 single one day, 24 hours, of time off. What constitutes 48 hours here?