Help with Date functions

  • Thread starter Thread starter Ayo
  • Start date Start date
A

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
 
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
 
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
Click to expand...


Perhaps (assuming your week starts on Sunday):

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