Last Day of the Month & What Day? (Excel97) 2

[Sylv4n]

How can I create a loop that will go through each day in any se month and print that in column "A" and print what day of the week it is in Col "B"

What I have at the momebnt is:

--------------------------
Dim strDate As Date
Dim strMonth As String
Dim lngRow As Long

'Gets the Month and Year from Sheet 1 Cells A & B
strDate = "01/" & Sheets("Sheet1&quot.Cells(1, "A&quot & "/" & Sheets("Sheet1&quot.Cells(1, "B&quot

strMonth = Month(strDate)
lngRow = 2 'The row to write in

While strMonth = Month(strDate)
Sheets("CALANDER&quot.Cells(lngRow, "A&quot = strDate
Sheets("CALANDER&quot.Cells(lngRow, "B&quot = WeekDay(strDate, vbSunday)

strDate = Day(strDate) + 1 & "/" & Month(strDate) & "/" & Year(strDate)
lngRow = lngRow + 1

Wend
--------------------------

Problems with the above Code:
1 - It will add a day until it hits the end of the month and then it will not go to the next month but will bring up a error, I guess I could trap that error but its a bit untidy, I rould rather a FOR x = 1 to 30 LOOP
2 - The day is in 1 to 7 not Monday, Tuesday Etc. I haev tried using the Format Function but I cant get anywhere, Again I could have SELECT CASE statement bit I would prefer VBA to do thw work for me.

Thanks In Advance

 

[SkipVought]

Hi,

You know that if you FORMAT a date "ddd" or "dddd", it will display the day of the week. Make another column as save yourself some coding, unless you need to perform an academic exersize.



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Sylv4n]

Excelent that worked a treat
Changeing:

Sheets("CALANDER&quot.Cells(lngRow, "B&quot = WeekDay(strDate, vbSunday)

TO

Sheets("CALANDER&quot.Cells(lngRow, "B&quot = Format(WeekDay(strDate, vbSunday), "dddd", vbSunday)

Works Excelent
I had everything other than the "dddd" in the abaove statement
THANKYOU )

Any Idea on how to loop until the end of the month?

 

[Bowers74]

I am lost as to why this even has to be done using code, since the results are obviously being returned to a sheet.

Use the date functions provided for you in Excel.

Read my FAQ: faq68-4037

I hope this helps!



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[Sylv4n]

The reason for using code is tath I want to do more advanced things than just displaying all days in a list.
I have looked at your FAQ and the solutions dont seem to work, you may haev a more update dversion of Excel (i'm only in 97.
But your pist did point me in the right direction as you can do:
strDate = strDate -1
while it is one month ahed, Thank you both

 

[SkipVought]

If you need to loop, you incriment the day in a Do...Loop and test the month.



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Sylv4n]

I tried that but the problem was it was crashing when I said:

strDate = lngDay & "/" Month(strMonth) & "/" & Year(StrYear)

and lngday was 32, but the above solution worked anyway

 

[Bowers74]

Which one of the formulae in my FAQ didn't work?

Do you have the Analysis ToolPak Add-in installed?

If something doesn't work correctly in my FAQ, then let me know, please, so that I can fix it, or at least inform people that a particular formula doesn't work in Excel '97.



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[Bowers74]

If you insist,

Sub Weekdays_Till_EOM()
Dim sDate As Long
sDate = DateSerial(Year(Now), Month(Now), 1) - 1
[A1].Select
Do
    sDate = sDate + 1
    ActiveCell = Format(Weekday(sDate, vbSunday), "dddd")
    ActiveCell.Offset(1, 0).Select
Loop Until Month(sDate) <> Month(Now)
End Sub

That should put you on the right track. ;-)

I hope this helps!


Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[combo]

Hi all,
sorry for coming at the end, but two things seem to be important here.

First - Skip's hint: just without VBA, format cell with date using custom format dddd (or ddd for short) and cell will remain a date, but will display the day of week.

Second - happily there was Sunday on the 1st of January 1900
(or at least MS calendars show so), so Mike's method works. Otherwise Weekday(#1/1/1900#, vbSunday) would not be 1. Anyway, the simplest way to get the weekday using Format function is Format(sDate,"dddd&quot.

combo

 

[Sylv4n]

The part that dident work in the FAQ was;
=EOMONTH(TODAY(),0)
it was saying that EOMONTH did not exist as a keyword

and as for
"Do you have the Analysis ToolPak Add-in installed?"
the answer is probably no.

And although your code would wok it is limited by the (Now) function which will only make it the curent month that can be recorded, if you follow me?

 

[Bowers74]

To install the Analysis ToolPak, go to Tools->Add-Ins... and check the Analysis ToolPak from the list. I believe that it was part of the Office '97 Suite as well.

As for the NOW() and/or TODAY() functions in my examples. I used these functions to demonstrate the examples only. If you want to have the EOMONTH() formula refer to another month, then just change the TODAY() to a date in that month or to a cell reference containing the date.

Good Luck!

Peace!

Mike

Never say Never!!!
Nothing is impossible!!!