Selected Answer
Hi again MrEMann
The attached file shows one way to add dates but skipping a given day of the week.
I've created a variable SkipDay and set it to 7 (which means Sundays, whereas 2 would omit Tuesdays say). Press the key combination Ctrl+Shift+J and it will add dates, provided there's a "seed" date in cell B2. I've added comments below so you can see what's happening (and added a custom format in column B so it's easy to see if the chosen day of the week is indeed skipped):
Sub AddDates()
'
' Keyboard Shortcut: Ctrl+Shift+J
'
Dim SkipDay As Integer, n As Long
' set the week day to skip (where Monday=1, to Sunday=7)
SkipDay = 7
With Sheet1
' check B2 is a date
If Not IsDate(.Cells(2, 2).Value) Then
.Cells(2, 2).Select
MsgBox "Please put a valid date in cell B2"
Exit Sub
End If
'clear any values
.Range(.Cells(3, 2), .Cells(200, 2)).ClearContents
' find last used cell in column 1
LstRw = .Cells(.Rows.Count, 1).End(xlUp).Row
' loop in column 2 from 3 to last row
For n = 3 To LstRw
' add a day to date in previous row
.Cells(n, 2).Value = Cells(n - 1, 2).Value + 1
' if the resulting date is a day to skip, add one to that date
If Weekday(.Cells(n, 2).Value, vbMonday) = SkipDay Then
.Cells(n, 2).Value = Cells(n, 2).Value + 1
End If
Next n
End With
MsgBox "Added dates (less " & WeekdayName(SkipDay, 0, vbMonday) & "s) to cells B3 to B" & LstRw
End Sub
Hope this fixes your problem. If so, please be sure to mark this Answer as Selected.
