Previous Topic Next Topic

Vba excel problem

Closed
Pm - Jul 27, 2010 at 05:20 AM
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Jul 30, 2010 at 09:06 AM
Hello,

Does anyone know the vba to do the following:

On command button select
- cell A3 = (1st of next month)
- cell A4 = (14th of the month or the next working day)

Can somebody give me an idea on how to get started??

Thanks in advance

PM

Excel 2003

1 reply

Answer 1 / 1
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 767
Jul 30, 2010 at 09:06 AM
You can try this

Dim FirstOfMonth As Date
Dim FourteenOfMonth As Date

FirstOfMonth = CDate(DateSerial(Year(Date), Month(Date) + 1, 1))
FourteenOfMonth = CDate(DateSerial(Year(Date), Month(Date) + 1, 14))

' if day is a saturday, then add 2 days
If (Weekday(FourteenOfMonth, vbMonday) = 6) Then
FourteenOfMonth = DateAdd("d", 2, FourteenOfMonth)

' if day is a sunday, then add 1 day
ElseIf (Weekday(FourteenOfMonth, vbMonday) = 7) Then
FourteenOfMonth = DateAdd("d", 1, FourteenOfMonth)
End If


Cells(3, 1) = FirstOfMonth
Cells(4, 1) = FourteenOfMonth


Of course this does not take into account if here is a holiday
0