VBA Excel code that autopopulates the next day's date, but only for weekdays

1
votes

I have this bit of code that just hangs out in a sheet located in a VBA project that autopopulates the date for an excel form. I've been tinkering with it to get it to populate the day's date when an individual enters a name into cell I6. In line 8,

r.Offset(0, 12).Value = Date + 1

I've added a 1 to populate the next day's date. Now I want go a little further with this to enter the next weekday's date if on a Friday. So every weekday, when a user enters data into cell I6, it inserts the next weekday's date into a cell 12 columns down in the same range, except Friday, where I want the date populated to be Monday of the next week. 

Private Sub Worksheet_Change(ByVal Target As Range)
     Dim I As Range, AF As Range, Inte As Range, r As Range
     Set I = Range("I6")
     Set Inte = Intersect(I, Target)
     If Inte Is Nothing Then Exit Sub
     Application.EnableEvents = False
         For Each r In Inte
             r.Offset(0, 12).Value = Date + 1
         Next r
     Application.EnableEvents = True
End Sub

Would I need an if statement? And if so, can I put that in the For loop(?). I'm not a coder, I need this to simplify my life at work. Any guidance would be appreciated.

2
vbaexcelexcel-2010

2 Answers

3
votes

There is already a function to skip weekends. No need to re-invent the wheel. Just make use of the WORKDAY function 

r.Offset(0, 12) = Application.WorksheetFunction.Workday(Date, 1)
2
votes

Add an If statement to check if today is Friday:

Private Sub Worksheet_Change(ByVal Target As Range)
     Dim I As Range, AF As Range, Inte As Range, r As Range
     Set I = Range("I6")
     Set Inte = Intersect(I, Target)
     If Inte Is Nothing Then Exit Sub
     Application.EnableEvents = False
         For Each r In Inte
            If Format(Date, "dddd") = "Friday" Then
                r.Offset(0, 12).Value = Date + 3
            Else
                r.Offset(0, 12).Value = Date + 1
            End If
         Next r
     Application.EnableEvents = True
End Sub