Vba excel problem Closed

Question

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

rizvisa1 · Answer

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

Vba excel problem

1 response

Similar discussions

Subscribe To Our Newsletter!