Aaron719
01-05-2014, 12:20 PM
Below is the code I have written. What I want it to do is perform the nested IF functions if "Auto" is selected or pop up an input box if "Manual" is selected. Instead of the input box only popping up once after I select "Manual", it also pops up when I select "Auto" and it will pop up for every cell I have this function in that is dependent on another cell that says "Manual". Essentially it forces me to answer input boxes that have already been answered and are not linked in anyway to the cell I am changing.
To clarify, I want to select "Manual" from a drop-down and I get an input box to put a number in, then I'd hit "OK" and it fills the appropriate cell with that number. If I select "Auto", I want the nested If function to preform and no input boxes.
Thank you!
Function EHours(hVal As String, iVal As String, dVal As String, xVal As String, yVal As String, wVal As String, inputVal As String) As Double
If inputVal = "Manual" Then
EHours = Application.InputBox(prompt:="Hours Worked =", Title:="Hours Worked.", Type:=1)
ElseIf inputVal = "Auto" Then
If iVal = "Daily" Then
EHours = hVal
ElseIf iVal = "Weekly" Then
If Month(dVal) = yVal And Day(dVal) = xVal Then
EHours = hVal
ElseIf wVal = WeekdayName(Weekday(dVal)) Then
EHours = hVal
Else:
EHours = 0
End If
ElseIf iVal = "Bi-Weekly" Then
If Month(dVal) = yVal And Day(dVal) = xVal Then
EHours = hVal
ElseIf (Month(DateAdd("d", 14, dVal)) = yVal And Day(DateAdd("d", 14, dVal)) = xVal) Or (Month(DateAdd("d", 14 * 2, dVal)) = yVal And Day(DateAdd("d", 14 * 2, dVal)) = xVal) _
Or (Month(DateAdd("d", 14 * 3, dVal)) = yVal And Day(DateAdd("d", 14 * 3, dVal)) = xVal) Or (Month(DateAdd("d", 14 * 4, dVal)) = yVal And Day(DateAdd("d", 14 * 4, dVal)) = xVal) _
Or (Month(DateAdd("d", 14 * 5, dVal)) = yVal And Day(DateAdd("d", 14 * 5, dVal)) = xVal) Or (Month(DateAdd("d", 14 * 6, dVal)) = yVal And Day(DateAdd("d", 14 * 6, dVal)) = xVal) _
Or (Month(DateAdd("d", 14 * 7, dVal)) = yVal And Day(DateAdd("d", 14 * 7, dVal)) = xVal) Or (Month(DateAdd("d", 14 * 8, dVal)) = yVal And Day(DateAdd("d", 14 * 8, dVal)) = xVal) _
Or (Month(DateAdd("d", 14 * 9, dVal)) = yVal And Day(DateAdd("d", 14 * 9, dVal)) = xVal) Or (Month(DateAdd("d", 14 * 10, dVal)) = yVal And Day(DateAdd("d", 14 * 10, dVal)) = xVal) _
Or (Month(DateAdd("d", 14 * 11, dVal)) = yVal And Day(DateAdd("d", 14 * 11, dVal)) = xVal) Or (Month(DateAdd("d", 14 * 12, dVal)) = yVal And Day(DateAdd("d", 14 * 12, dVal)) = xVal) _
Or (Month(DateAdd("d", 14 * 13, dVal)) = yVal And Day(DateAdd("d", 14 * 13, dVal)) = xVal) Or (Month(DateAdd("d", 14 * 14, dVal)) = yVal And Day(DateAdd("d", 14 * 14, dVal)) = xVal) _
Or (Month(DateAdd("d", 14 * 15, dVal)) = yVal And Day(DateAdd("d", 14 * 15, dVal)) = xVal) Or (Month(DateAdd("d", 14 * 16, dVal)) = yVal And Day(DateAdd("d", 14 * 16, dVal)) = xVal) _
Or (Month(DateAdd("d", 14 * 17, dVal)) = yVal And Day(DateAdd("d", 14 * 17, dVal)) = xVal) Or (Month(DateAdd("d", 14 * 18, dVal)) = yVal And Day(DateAdd("d", 14 * 18, dVal)) = xVal) _
Or (Month(DateAdd("d", 14 * 19, dVal)) = yVal And Day(DateAdd("d", 14 * 19, dVal)) = xVal) Or (Month(DateAdd("d", 14 * 20, dVal)) = yVal And Day(DateAdd("d", 14 * 20, dVal)) = xVal) _
Or (Month(DateAdd("d", 14 * 21, dVal)) = yVal And Day(DateAdd("d", 14 * 21, dVal)) = xVal) Or (Month(DateAdd("d", 14 * 22, dVal)) = yVal And Day(DateAdd("d", 14 * 22, dVal)) = xVal) _
Or (Month(DateAdd("d", 14 * 23, dVal)) = yVal And Day(DateAdd("d", 14 * 23, dVal)) = xVal) Or (Month(DateAdd("d", 14 * 24, dVal)) = yVal And Day(DateAdd("d", 14 * 24, dVal)) = xVal) _
Or (Month(DateAdd("d", 14 * 25, dVal)) = yVal And Day(DateAdd("d", 14 * 25, dVal)) = xVal) Then
EHours = hVal
Else:
EHours = 0
End If
ElseIf iVal = "Monthly" Then
If Month(dVal) = yVal And Day(dVal) = xVal Then
EHours = hVal
ElseIf (Month(DateAdd("m", 1, dVal)) = yVal And Day(DateAdd("m", 1, dVal)) = xVal) Or (Month(DateAdd("m", 2, dVal)) = yVal And Day(DateAdd("m", 1, dVal)) = xVal) _
Or (Month(DateAdd("m", 3, dVal)) = yVal And Day(DateAdd("m", 1, dVal)) = xVal) Or (Month(DateAdd("m", 4, dVal)) = yVal And Day(DateAdd("m", 1, dVal)) = xVal) _
Or (Month(DateAdd("m", 5, dVal)) = yVal And Day(DateAdd("m", 1, dVal)) = xVal) Or (Month(DateAdd("m", 6, dVal)) = yVal And Day(DateAdd("m", 1, dVal)) = xVal) _
Or (Month(DateAdd("m", 7, dVal)) = yVal And Day(DateAdd("m", 1, dVal)) = xVal) Or (Month(DateAdd("m", 8, dVal)) = yVal And Day(DateAdd("m", 1, dVal)) = xVal) _
Or (Month(DateAdd("m", 9, dVal)) = yVal And Day(DateAdd("m", 1, dVal)) = xVal) Or (Month(DateAdd("m", 10, dVal)) = yVal And Day(DateAdd("m", 1, dVal)) = xVal) _
Or (Month(DateAdd("m", 11, dVal)) = yVal And Day(DateAdd("m", 1, dVal)) = xVal) Or (Month(DateAdd("m", 12, dVal)) = yVal And Day(DateAdd("m", 1, dVal)) = xVal) Then
EHours = hVal
Else
EHours = 0
End If
Else:
EHours = "0"
End If
End If
End Function
To clarify, I want to select "Manual" from a drop-down and I get an input box to put a number in, then I'd hit "OK" and it fills the appropriate cell with that number. If I select "Auto", I want the nested If function to preform and no input boxes.
Thank you!
Function EHours(hVal As String, iVal As String, dVal As String, xVal As String, yVal As String, wVal As String, inputVal As String) As Double
If inputVal = "Manual" Then
EHours = Application.InputBox(prompt:="Hours Worked =", Title:="Hours Worked.", Type:=1)
ElseIf inputVal = "Auto" Then
If iVal = "Daily" Then
EHours = hVal
ElseIf iVal = "Weekly" Then
If Month(dVal) = yVal And Day(dVal) = xVal Then
EHours = hVal
ElseIf wVal = WeekdayName(Weekday(dVal)) Then
EHours = hVal
Else:
EHours = 0
End If
ElseIf iVal = "Bi-Weekly" Then
If Month(dVal) = yVal And Day(dVal) = xVal Then
EHours = hVal
ElseIf (Month(DateAdd("d", 14, dVal)) = yVal And Day(DateAdd("d", 14, dVal)) = xVal) Or (Month(DateAdd("d", 14 * 2, dVal)) = yVal And Day(DateAdd("d", 14 * 2, dVal)) = xVal) _
Or (Month(DateAdd("d", 14 * 3, dVal)) = yVal And Day(DateAdd("d", 14 * 3, dVal)) = xVal) Or (Month(DateAdd("d", 14 * 4, dVal)) = yVal And Day(DateAdd("d", 14 * 4, dVal)) = xVal) _
Or (Month(DateAdd("d", 14 * 5, dVal)) = yVal And Day(DateAdd("d", 14 * 5, dVal)) = xVal) Or (Month(DateAdd("d", 14 * 6, dVal)) = yVal And Day(DateAdd("d", 14 * 6, dVal)) = xVal) _
Or (Month(DateAdd("d", 14 * 7, dVal)) = yVal And Day(DateAdd("d", 14 * 7, dVal)) = xVal) Or (Month(DateAdd("d", 14 * 8, dVal)) = yVal And Day(DateAdd("d", 14 * 8, dVal)) = xVal) _
Or (Month(DateAdd("d", 14 * 9, dVal)) = yVal And Day(DateAdd("d", 14 * 9, dVal)) = xVal) Or (Month(DateAdd("d", 14 * 10, dVal)) = yVal And Day(DateAdd("d", 14 * 10, dVal)) = xVal) _
Or (Month(DateAdd("d", 14 * 11, dVal)) = yVal And Day(DateAdd("d", 14 * 11, dVal)) = xVal) Or (Month(DateAdd("d", 14 * 12, dVal)) = yVal And Day(DateAdd("d", 14 * 12, dVal)) = xVal) _
Or (Month(DateAdd("d", 14 * 13, dVal)) = yVal And Day(DateAdd("d", 14 * 13, dVal)) = xVal) Or (Month(DateAdd("d", 14 * 14, dVal)) = yVal And Day(DateAdd("d", 14 * 14, dVal)) = xVal) _
Or (Month(DateAdd("d", 14 * 15, dVal)) = yVal And Day(DateAdd("d", 14 * 15, dVal)) = xVal) Or (Month(DateAdd("d", 14 * 16, dVal)) = yVal And Day(DateAdd("d", 14 * 16, dVal)) = xVal) _
Or (Month(DateAdd("d", 14 * 17, dVal)) = yVal And Day(DateAdd("d", 14 * 17, dVal)) = xVal) Or (Month(DateAdd("d", 14 * 18, dVal)) = yVal And Day(DateAdd("d", 14 * 18, dVal)) = xVal) _
Or (Month(DateAdd("d", 14 * 19, dVal)) = yVal And Day(DateAdd("d", 14 * 19, dVal)) = xVal) Or (Month(DateAdd("d", 14 * 20, dVal)) = yVal And Day(DateAdd("d", 14 * 20, dVal)) = xVal) _
Or (Month(DateAdd("d", 14 * 21, dVal)) = yVal And Day(DateAdd("d", 14 * 21, dVal)) = xVal) Or (Month(DateAdd("d", 14 * 22, dVal)) = yVal And Day(DateAdd("d", 14 * 22, dVal)) = xVal) _
Or (Month(DateAdd("d", 14 * 23, dVal)) = yVal And Day(DateAdd("d", 14 * 23, dVal)) = xVal) Or (Month(DateAdd("d", 14 * 24, dVal)) = yVal And Day(DateAdd("d", 14 * 24, dVal)) = xVal) _
Or (Month(DateAdd("d", 14 * 25, dVal)) = yVal And Day(DateAdd("d", 14 * 25, dVal)) = xVal) Then
EHours = hVal
Else:
EHours = 0
End If
ElseIf iVal = "Monthly" Then
If Month(dVal) = yVal And Day(dVal) = xVal Then
EHours = hVal
ElseIf (Month(DateAdd("m", 1, dVal)) = yVal And Day(DateAdd("m", 1, dVal)) = xVal) Or (Month(DateAdd("m", 2, dVal)) = yVal And Day(DateAdd("m", 1, dVal)) = xVal) _
Or (Month(DateAdd("m", 3, dVal)) = yVal And Day(DateAdd("m", 1, dVal)) = xVal) Or (Month(DateAdd("m", 4, dVal)) = yVal And Day(DateAdd("m", 1, dVal)) = xVal) _
Or (Month(DateAdd("m", 5, dVal)) = yVal And Day(DateAdd("m", 1, dVal)) = xVal) Or (Month(DateAdd("m", 6, dVal)) = yVal And Day(DateAdd("m", 1, dVal)) = xVal) _
Or (Month(DateAdd("m", 7, dVal)) = yVal And Day(DateAdd("m", 1, dVal)) = xVal) Or (Month(DateAdd("m", 8, dVal)) = yVal And Day(DateAdd("m", 1, dVal)) = xVal) _
Or (Month(DateAdd("m", 9, dVal)) = yVal And Day(DateAdd("m", 1, dVal)) = xVal) Or (Month(DateAdd("m", 10, dVal)) = yVal And Day(DateAdd("m", 1, dVal)) = xVal) _
Or (Month(DateAdd("m", 11, dVal)) = yVal And Day(DateAdd("m", 1, dVal)) = xVal) Or (Month(DateAdd("m", 12, dVal)) = yVal And Day(DateAdd("m", 1, dVal)) = xVal) Then
EHours = hVal
Else
EHours = 0
End If
Else:
EHours = "0"
End If
End If
End Function