Help with Date functions

[Ayo]

How do I ensure that, in the "ElseIf Weekday(Date) > 2 Then" statement of
the sub below "ws.Range("J6").Value" will always be the Tuesday of the week
of "ws.Range("F7").Value".


Private Sub Workbook_Open()
Dim ws As Worksheet
Const PWORD As String = "Ayo"
Application.ScreenUpdating = False

If Me.Name = "Northeast AAV Project Outlook_ver2.xls" Then
If Weekday(Date) = 2 Then
For Each ws In Worksheets
If ws.Name <> "BO Download" Then
ws.Visible = True
ws.Range("F7").Value = Date
ws.Range("J6").Value = Date + 1
End If
Next
ElseIf Weekday(Date) > 2 Then
For Each ws In Worksheets
If ws.Name <> "BO Download" Then
ws.Visible = True
ws.Range("F7").Value = Date
ws.Range("J6").Value = Date + 1
End If
Next
End If
Else
Exit Sub
End If
Application.ScreenUpdating = True
End Sub

 

[JLGWhiz]

Since it is not specified where the value "ws.Range("J6").Value" comes from,
the only answer would be to check if it equals 3, which would be the day of
the week value for Tuesday. You can do that with an If Then statement:

If ws.Range("J6").Value = 3 Then
ws.Range("J6").Value = Date + 1
Else
'some other action
End If

 

[Ron Rosenfeld]

How do I ensure that, in the "ElseIf Weekday(Date) > 2 Then" statement of
the sub below "ws.Range("J6").Value" will always be the Tuesday of the week
of "ws.Range("F7").Value".


Private Sub Workbook_Open()
Dim ws As Worksheet
Const PWORD As String = "Ayo"
Application.ScreenUpdating = False

If Me.Name = "Northeast AAV Project Outlook_ver2.xls" Then
If Weekday(Date) = 2 Then
For Each ws In Worksheets
If ws.Name <> "BO Download" Then
ws.Visible = True
ws.Range("F7").Value = Date
ws.Range("J6").Value = Date + 1
End If
Next
ElseIf Weekday(Date) > 2 Then
For Each ws In Worksheets
If ws.Name <> "BO Download" Then
ws.Visible = True
ws.Range("F7").Value = Date
ws.Range("J6").Value = Date + 1
End If
Next
End If
Else
Exit Sub
End If
Application.ScreenUpdating = True
End Sub

Perhaps (assuming your week starts on Sunday):

ws.Range("J6").Value = Date + 3 - weekday(date)
--ron