Solved: Please check one line of my code

**BENSON** · 12-19-2007, 11:48 PM

I have a work book with seven sheets labeled MON TUES WED THURS SAT SUN MON.The code below we only work if it runs on a Wednesday the line of code which seems to be the problem is

[VBA]
With Worksheets(Format(Weekday(Date), "ddd"))
[/VBA]

The whole code is below

[VBA]Private Sub Workbook_Open()
With Worksheets(Format(Weekday(Date), "ddd"))
.Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0).Value = Date
End With
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim wSheet As Worksheet
Application.ScreenUpdating = False
For Each wSheet In Worksheets

Next

Dim Arr, Dy As Long
Dim Rng As Range, cell As Range
Dim Start As Boolean
Dim Prompt As String, RngStr As String

Prompt = "PLEASE CHECK YOUR DATA ENSURING ALL REQUIRED " & _
"CELLS ARE COMPLETE." & vbCrLf & "YOU WILL NOT BE ABLE " & _
"TO CLOSE OR SAVE THE DAILY REPORT UNTIL ALL THE REQUIRED CELLS ARE FILLED " & _
"OUT COMPLETELY. " & vbCrLf & vbCrLf & _
"THE CELLS LISTED BELOW CONTAIN NO DATA AND HAVE BEEN HIGHLIGHTED RED:" _
& vbCrLf & vbCrLf

Arr = Array("TUES", "WED", "THURS", "FRI", "SAT", "SUN", "MON")
For Dy = 0 To Weekday(Now, vbTuesday) - 1
With Worksheets(Format(Weekday(Date), "ddd"))
Start = True
Set Rng = TgtRange(.Name)
'highlights the blank cells
For Each cell In Rng
If cell.Value = vbNullString Then
cell.Interior.ColorIndex = 3 '** color red
If Start Then RngStr = RngStr & cell.Parent.Name & vbCrLf
Start = False
RngStr = RngStr & cell.Address(False, False) & " , "
Else
cell.Interior.ColorIndex = 0 '** NO color

End If
Next
If RngStr <> "" Then RngStr = Left$(RngStr, Len(RngStr) - 0)
End With
Next
If RngStr <> "" Then
MsgBox Prompt & RngStr, vbCritical, "Incomplete Data"
Cancel = True
Else
'saves the changes before closing
ThisWorkbook.Save
Cancel = False
End If

For Each wSheet In Worksheets

Next
Application.ScreenUpdating = True
End Sub

Function TgtRange(ShtName As String) As Range
With Sheets(ShtName)
Select Case ShtName

Case "TUES"
Set TgtRange = .Cells(.Rows.Count, "a").End(xlUp).Resize(, 26)
Case "WED"
Set TgtRange = .Cells(.Rows.Count, "a").End(xlUp).Resize(, 26)
Case "THURS"
Set TgtRange = .Cells(.Rows.Count, "a").End(xlUp).Resize(, 26)
Case "FRI"
Set TgtRange = .Cells(.Rows.Count, "a").End(xlUp).Resize(, 26)
Case "SAT"
Set TgtRange = .Cells(.Rows.Count, "a").End(xlUp).Resize(, 26)
Case "SUN"
Set TgtRange = .Cells(.Rows.Count, "a").End(xlUp).Resize(, 26)
Case "MON"
Set TgtRange = .Cells(.Rows.Count, "a").End(xlUp).Resize(, 26)
End Select
End With
End Function[/VBA]

Thanks for any help

**Andy Pope** · 12-20-2007, 02:00 AM

Today it will problem only run on sheet THU

Within the loop you are using the sheet based on todays date rather than an element from the array. So each time it will be the same sheet.
Use the loop index variable Dy when constructing the sheet name.
[vba]
Arr = Array("TUES", "WED", "THURS", "FRI", "SAT", "SUN", "MON")
For Dy = 0 To Weekday(Now, vbTuesday) - 1
With Worksheets(Format( Dy , "ddd"))

' force upper case so the select in TgtRange will work regardless of actual sheet name case.
Set Rng = TgtRange(ucase(.Name))

[/vba]

The for loop above goes from zero to the value for the current day. You may want to process all the days when the workbook closes. If so then use

[vba]
Arr = Array("TUES", "WED", "THURS", "FRI", "SAT", "SUN", "MON")
For Dy = lbound(arr) to ubound(arr)
With Worksheets(Format( arr(Dy) , "ddd"))
[/vba]

Or if you only want to do current day remove the loop and just set the variable Dy to the correct value.

**Bob Phillips** · 12-20-2007, 02:55 AM

Seems a bit of overkill Andy

[vba]

For dy = LBound(arr) To UBound(arr)
With Worksheets(Format(arr(dy), "ddd"))
[/vba]

all you need is

[vba]

For dy = LBound(arr) To UBound(arr)
With Worksheets(arr(dy))
[/vba]

**Andy Pope** · 12-20-2007, 03:21 AM

Your right Bob, it is actual completely redundent

The value being formatted would be "THU" so actually does not require formatting.

**BENSON** · 12-20-2007, 06:16 AM

Thanks for the help, the closing part of the macro works fine.However on opening the date gets posted in this instance ( To day being Thursday 20/12/07 ) into sheets ( SAT SUN MON) and not just THURS

THANKS

**Bob Phillips** · 12-20-2007, 06:21 AM

[vba]

Private Sub Workbook_Open()
Dim Arr
Dim Dy As String
Arr = Array("TUES", "WED", "THURS", "FRI", "SAT", "SUN", "MON")
Dy = Arr(Weekday(Now, vbTuesday))
With Worksheets(Dy)
.Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0).Value = Dy
End With
End Sub
[/vba]

**rory** · 12-20-2007, 06:22 AM

What code did you put in the Open event? Incidentally, I think you would find life easier if you changed the sheet names to 3 characters. Then formatting as "ddd" would actually match - i.e. MON, TUE, WED, THU, FRI, SAT, SUN
Also, your original post had two MON sheets and no FRI - was that correct?

**BENSON** · 12-20-2007, 06:50 AM

Have just copied code from El Xid this pastes the the word "FRI" into the friday sheet. Yes my original post I did make a mistake ,it should read TUES through to MON not to Mondays
[VBA]
Private Sub Workbook_Open()
Dim Arr
Dim Dy As String
Arr = Array("TUES", "WED", "THURS", "FRI", "SAT", "SUN", "MON")
Dy = Arr(Weekday(Now, vbTuesday))
With Worksheets(Dy)
.Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0).Value = Dy
End With
End Sub
[/VBA]

**Bob Phillips** · 12-20-2007, 06:57 AM

Sorry, I omitted catering for array bounds

[vba]

Private Sub Workbook_Open()
Dim Arr
Dim Dy As String
Arr = Array("TUES", "WED", "THURS", "FRI", "SAT", "SUN", "MON")
Dy = Arr(Weekday(Date, vbTuesday) - 1 + LBound(Arr))
With Worksheets(Dy)
.Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0).Value = Dy
End With
End Sub
[/vba]

**BENSON** · 12-20-2007, 07:12 AM

Nearly there,it pastes to the correct worksheet in this case THURS is it possible to show the date as well as the word "THUR" 1i: THURS 20th DEC 07

Thanks for all the help so far

**Norie** · 12-20-2007, 09:59 AM

Instead of Weekday(Date) just use Date.

If you used Weekday(Date) today the result would be 5.

Now since Excel stores dates as integers that's the equivalent of 5th January 1900 which was a Thursday but that could just be a coincidence.

**unmarkedhelicopter** · 12-21-2007, 04:44 AM

Norie, Ah ! be careful Mac's interpret dates differently (I'm probably making this up but I think they start at 1908), though I've yet to see a definitive statement from a mac user. On this or date interpretation differences. To me this seems idiotic as if I write a date which is stored as 412 then whether it gets opened on a PC or a MAC I'd still like to see the same date. Maybe it's an urban legend !!! ...

Any MAC user PLEASE Comment !!!

Edit: I wasn't making it up, but I did get the date wrong, MAC's assume 1904/01/01 as 1 not 1900/01/01

Edit2: Just to stop you PC user's getting smug (about what I don't know) at least the above "feature" stops them having a bug which tells you that 1900/02/29 is a valid day, funny cos 1900 is NOT a leap year.

**Bob Phillips** · 12-21-2007, 04:51 AM

My posts just don't seem to be stcking at the moment. I did this yesterday, but it's not here, so I'll repeat it

[vba]

Private Sub Workbook_Open()
Dim Arr
Dim Dy As String
Arr = Array("TUES", "WED", "THURS", "FRI", "SAT", "SUN", "MON")
Dy = Arr(Weekday(Date, vbTuesday) - 1 + LBound(Arr))
With Worksheets(Dy)
.Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0).Value = Dy & " " & _
Format(Date, "d mmmm yyyy")
End With
End Sub
[/vba]

**Norie** · 12-21-2007, 08:34 AM

unmarkedhelicopter

That's sort of my point, using just Date will avoid any problems as to how dates are stored or where the start point is.

Like I said it's probably just luck that yesterday was Thursday and so was 5th January 1900.

**BENSON** · 12-24-2007, 05:27 AM

WORKING PERFECT THANKS TO YOU ALL

Thread: Solved: Please check one line of my code

Thread Tools

Display

Solved: Please check one line of my code

Posting Permissions