Help with Date functions
 

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

Help with Date functions
 

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






"Ayo" wrote in message
...
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

Help with Date functions
 

On Mon, 23 Nov 2009 15:06:01 -0800, Ayo wrote:

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