Hello,
I have a workbook with 26 paysheets, which includes the dates of a given pay
period. I'd like a macro that will find the cells where a holiday occurs.
Christmas was quite easy to do as it anyways appears in the last paysheet.

Veteran's day, however, is more challenging because it can occcur in one of
two pay periods. I'd like a macro that will find that date. When I record
that macro it hardcodes the name of the worksheet. This doesn't work for me
because the worksheet name changes from year to year.

I was invisioning a do while loop that'll search each worksheet for each
holiday.

For instance,
Sub DoWhile()
Do while (condition is true)
Perform task
Loop
End sub

Loop would end when date is found. Is there an IsFound() boolean function
in VBA that would make this happen?

Thanks in advance for your help,
Ellen

Function GetVeteransDay(lYear As Long) As Date

Dim daDate As Date

daDate = DateSerial(lYear, 11, 11)

Select Case Weekday(daDate, vbMonday)
Case 6
GetVeteransDay = daDate - 1
Case 7
GetVeteransDay = daDate + 1
Case Else
GetVeteransDay = daDate
End Select

End Function


Sub test()

MsgBox Format(GetVeteransDay(2007), "ddd dd/mmm/yyyy")

End Sub


RBS


"EllenM" wrote in message
...
Hello,
I have a workbook with 26 paysheets, which includes the dates of a given
pay
period. I'd like a macro that will find the cells where a holiday occurs.
Christmas was quite easy to do as it anyways appears in the last paysheet.

Veteran's day, however, is more challenging because it can occcur in one
of
two pay periods. I'd like a macro that will find that date. When I
record
that macro it hardcodes the name of the worksheet. This doesn't work for
me
because the worksheet name changes from year to year.

I was invisioning a do while loop that'll search each worksheet for each
holiday.

For instance,
Sub DoWhile()
Do while (condition is true)
Perform task
Loop
End sub

Loop would end when date is found. Is there an IsFound() boolean function
in VBA that would make this happen?

Thanks in advance for your help,
Ellen

Thanks so much. Your code was very useful!!!

"EllenM" wrote:

Hello,
I have a workbook with 26 paysheets, which includes the dates of a given pay
period. I'd like a macro that will find the cells where a holiday occurs.
Christmas was quite easy to do as it anyways appears in the last paysheet.

Veteran's day, however, is more challenging because it can occcur in one of
two pay periods. I'd like a macro that will find that date. When I record
that macro it hardcodes the name of the worksheet. This doesn't work for me
because the worksheet name changes from year to year.

I was invisioning a do while loop that'll search each worksheet for each
holiday.

For instance,
Sub DoWhile()
Do while (condition is true)
Perform task
Loop
End sub

Loop would end when date is found. Is there an IsFound() boolean function
in VBA that would make this happen?

Thanks in advance for your help,
Ellen