Application-defined or object-defined error
 

I need help with this error. I get the error on line
"ws.Range("E7").Value = mondaysDate(Weekday(Date)) - 7"
The function run fine, its just that the erroe comes up when control is
returned to the subroutine. And I don't know what the problem is. Any help
will be appreciated.
Thanks.

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("E7").Value = Date - 7
ws.Range("F7").Value = Date
ws.Range("J6").Value = Date
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("E7").Value = mondaysDate(Weekday(Date)) - 7
ws.Range("J6").Value = mondaysDate(Weekday(Date))
End If
Next
End If
Else
Exit Sub
End If
Application.ScreenUpdating = True
End Sub

Function mondaysDate(dayNumber) As Date
Select Case dayNumber
Case 1
mondayDate = Date - 6
Case 3
mondayDate = Date - 1
Case 4
mondayDate = Date - 2
Case 5
mondayDate = Date - 3
Case 6
mondayDate = Date - 4
Case 7
mondayDate = Date - 5
End Select
End Function

Application-defined or object-defined error
 

Ayo

--Within the function you have mentioned 'mondayDate' and not 'mondayDates'.
The 's' is missing. Use the Option Explicit statement on top of your module
to avoid such errors

--To find previous mondays date you can try the below
MsgBox Date - (Weekday(Date)) - 5

If this post helps click Yes
---------------
Jacob Skaria


"Ayo" wrote:

I need help with this error. I get the error on line
"ws.Range("E7").Value = mondaysDate(Weekday(Date)) - 7"
The function run fine, its just that the erroe comes up when control is
returned to the subroutine. And I don't know what the problem is. Any help
will be appreciated.
Thanks.

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("E7").Value = Date - 7
ws.Range("F7").Value = Date
ws.Range("J6").Value = Date
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("E7").Value = mondaysDate(Weekday(Date)) - 7
ws.Range("J6").Value = mondaysDate(Weekday(Date))
End If
Next
End If
Else
Exit Sub
End If
Application.ScreenUpdating = True
End Sub

Function mondaysDate(dayNumber) As Date
Select Case dayNumber
Case 1
mondayDate = Date - 6
Case 3
mondayDate = Date - 1
Case 4
mondayDate = Date - 2
Case 5
mondayDate = Date - 3
Case 6
mondayDate = Date - 4
Case 7
mondayDate = Date - 5
End Select
End Function