This Works Fine If the Employee Starts on the First of the Year if Not I need
to consider the Start Date. G4 holds Allotted Vacation Days (10 Vacation
days) and G5 Holds Alloted Personal/Sick Days (5 Sick/Personal Days). D3 is
the End Date field.

not sure how to go about this...Confused.,....


Private Sub Workbook_Open()
Dim MyItem As Double, MyItem2 As Double, ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Activate
If IsEmpty(Range("D3").Value) Then
MyItem = Month(Now) / 12 * Range("G4").Value
Else
MyItem = Month(Range("D3").Value) / 12 * Range("G4").Value
End If
Range("G8").Value = MyItem
If IsEmpty(Range("D3").Value) Then
MyItem2 = Month(Now) / 12 * Range("G5").Value
Else
MyItem2 = Month(Range("D3").Value) / 12 * Range("G5").Value
End If
Range("G13").Value = MyItem2
Next ws
End Sub

You have to subtract the start month from your curret month in the formula.


Month(Now) - month("Start Date")
or
Month(Range("D3").Value) - month("Start Date")

"O...." wrote:

This Works Fine If the Employee Starts on the First of the Year if Not I need
to consider the Start Date. G4 holds Allotted Vacation Days (10 Vacation
days) and G5 Holds Alloted Personal/Sick Days (5 Sick/Personal Days). D3 is
the End Date field.

not sure how to go about this...Confused.,....


Private Sub Workbook_Open()
Dim MyItem As Double, MyItem2 As Double, ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Activate
If IsEmpty(Range("D3").Value) Then
MyItem = Month(Now) / 12 * Range("G4").Value
Else
MyItem = Month(Range("D3").Value) / 12 * Range("G4").Value
End If
Range("G8").Value = MyItem
If IsEmpty(Range("D3").Value) Then
MyItem2 = Month(Now) / 12 * Range("G5").Value
Else
MyItem2 = Month(Range("D3").Value) / 12 * Range("G5").Value
End If
Range("G13").Value = MyItem2
Next ws
End Sub

Why not just use formulae to achieve what you want?

--
Hth

Kassie Kasselman
Change xxx to hotmail


"O...." wrote:

This Works Fine If the Employee Starts on the First of the Year if Not I need
to consider the Start Date. G4 holds Allotted Vacation Days (10 Vacation
days) and G5 Holds Alloted Personal/Sick Days (5 Sick/Personal Days). D3 is
the End Date field.

not sure how to go about this...Confused.,....


Private Sub Workbook_Open()
Dim MyItem As Double, MyItem2 As Double, ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Activate
If IsEmpty(Range("D3").Value) Then
MyItem = Month(Now) / 12 * Range("G4").Value
Else
MyItem = Month(Range("D3").Value) / 12 * Range("G4").Value
End If
Range("G8").Value = MyItem
If IsEmpty(Range("D3").Value) Then
MyItem2 = Month(Now) / 12 * Range("G5").Value
Else
MyItem2 = Month(Range("D3").Value) / 12 * Range("G5").Value
End If
Range("G13").Value = MyItem2
Next ws
End Sub