如果下一个Dateadd落在星期六或星期日，则将其移至星期一 [英] Move next Dateadd to Monday if it falls on Saturday or Sunday

问题描述

请使用VBA宏将下一个添加的下一个日期始终移动到星期一(如果它在星期六或星期日).我宁愿不使用公式.以下代码使添加的日期在一周中的任何时间下降.非常感谢您的帮助.请让我知道问题是否还不清楚，我将尝试重新制定.再次感谢.

Please I would like to move next date added always to Monday if it falls on Saturday or Sunday using VBA macro. I would prefer not to use formulas. The following code make date added fall anytime in the week. Many thanks for your help really appreciated. Plese let me know if the question is not clear and I would try to reformulate. Thanks again.

Private Sub Worksheet_Change(ByVal target As Range)
    Dim d1 As Date, d2 As Date, d3 As Date

    d1 = DateAdd("w", 1, Date)
    d2 = DateAdd("w", 7, Date)
    d3 = DateAdd("w", 3, Date)

    If Not Intersect(target, Range("H3:H150")) Is Nothing Then
        If target.Value = 7 Then
            target.Offset(0, 1).Value = d2
        ElseIf target.Value = 3 Then
            target.Offset(0, 1).Value = d3
        ElseIf target.Value = 1 Then
            target.Offset(0, 1).Value = d1
        Else
        End If
    End If
End Sub

推荐答案

类似的方法可能会帮助

Function NextMonday(dtDate As Date, lngDaysToAdd As Long)

Dim intDaysOffset As Integer

NextMonday = DateAdd("d", lngDaysToAdd, dtDate)

intDaysOffset = (7 - Weekday(NextMonday, vbMonday)) + 1

NextMonday = DateAdd("d", intDaysOffset, NextMonday)

End Function

这篇关于如果下一个Dateadd落在星期六或星期日，则将其移至星期一的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！