[RESOLVED] Add full month worth of data to worksheet

[MartinLiss]

I have data for a whole month that looks like this. The data includes all the days in December:

I'd like a macro that would add similar data for January 2023.

The general rule is that when the macro is executed, that if there is existing data, the data for the next month is added, otherwise the current month is added.

[Zvoni]

Haven’t understood your rule.
existing data of what? Current day?

[MartinLiss]

If the worksheet is empty except for the headings in rows 1 and 2, add the Day, Date and Time for all the days in the current month, otherwise add the Day, Date and Time for all the days in the next month.

For example if today's date is December 17th, 2022 and the worksheet is empty except for the headings in rows 1 and 2, add the Day, Date and Time for all the full month of December starting in row 3. Then when the macro is run again skip one row below the existing December data and add the Day, Date and Time for all the days in the January 2023.

[Zvoni]

Just check if A3 is empty.
then it‘s a simple loop with Step 5

[MartinLiss]

It's really not that simple. Things to consider:

1. How many days in the month?
2. What is the correct Day for the Date?
3. How to avoid adding a month 13?
4. "Step 5" is problematic for the last week in a month.

[Zvoni]

1) use DateAdd to get „LastDayOfMonth“. search for this. Gazillion examples
2) look at Format-Function. Or was it „weekday“? *hint*
3) use DateAdd
4) it‘s not. The step 5 is for the block of rows. And don‘t use a For-Loop

[Dan_W]

This seems straightforward. The only thing that seems to be changing is the day of the month - for which a simple loop would work. Date column and day of week column have the same values - you just change the formatting (D-MMM-YY v DDD). And you can get the days of the month through the EOMONTH function:

Function DaysInMonth(ByVal MonthValue As Long, Optional YearValue As Long = -1)
    If YearValue = -1 Then YearValue = Year(Now)
    DaysInMonth = Day(WorksheetFunction.EoMonth(DateSerial(YearValue, MonthValue, 1), 0))
End Function

[MartinLiss]

Thanks all. I worked it out.