How to autofill cells below a variable cell that can move within a range

[wbstadeli]

Hi Guys,

I am working on a Monthy time card, with Column B named Day of the week, which is all the weekdays Sunday - Saturday put on seperate rows, similar to the below table:


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Day of the week:[/TD]
[TD]Date:[/TD]
[/TR]
[TR]
[TD]Sunday[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Monday[/TD]
[TD]1/1/2018[/TD]
[/TR]
[TR]
[TD]Tuesday[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Wednesday[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Thursday[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Friday[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Saturday[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]This table continues like above for 6 weeks...[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


The first day of a month can land on any one of these days depending on the month, so i have a formula in the adjacent "Date" cell to auto-fill the correct weekday with the beginning of the month date.

This is the formula that im using to do this:
=IF(ISNUMBER(SEARCH("Monday",D3)),D2," ") ; Then the cell below this is the same except "Monday" is changed to "Tuesday" etc..

D3=TEXT( D2, "dddd")
D2=First day of the month (example: 1/1/2018, next month would be 2/1/2018, etc)

All of the above works great!



Here is my question, how do i get the rest of the month to autofill below the cell that gets filled in from the above formula? Example: For this month the first day of the month is on a Monday , so 1/1/18 will autofill on the cell as shown in the table. Now i need the rest of the months dates to fill in below that cell until the end of january so thru 1/31/2018. Is there a formula for this? or a code i could run?

Thanks for your help!

 

[Fluff]

How about

Sub MonthsDates()
   
   Dim Fnd As Range
   Dim Dys As Long
   Dim SDate As Date
   
   SDate = DateSerial(Year(Date), Month(Date), 1)
   Set Fnd = Columns(2).Find(Format(SDate, "dddd"), , , xlWhole, , , False, , False)
   Fnd.Offset(, 1).Value = SDate
   Dys = Day(WorksheetFunction.EoMonth(Date, 0))
   Fnd.Offset(, 1).AutoFill Fnd.Offset(, 1).Resize(Dys), xlFillDays

End Sub

This is based on the date the macro is run

 

[wbstadeli]

How about

Sub MonthsDates()
   
   Dim Fnd As Range
   Dim Dys As Long
   Dim SDate As Date
   
   SDate = DateSerial(Year(Date), Month(Date), 1)
   Set Fnd = Columns(2).Find(Format(SDate, "dddd"), , , xlWhole, , , False, , False)
   Fnd.Offset(, 1).Value = SDate
   Dys = Day(WorksheetFunction.EoMonth(Date, 0))
   Fnd.Offset(, 1).AutoFill Fnd.Offset(, 1).Resize(Dys), xlFillDays

End Sub

This is based on the date the macro is run

Thanks Fluff! So i wasn't able to get this to work, however im probably doing something wrong. But a key thing, is there a way to base this code from a date of in a cell instead of basing it of the day the macro is ran? Reason being is that i dont want it based of the month that a person would run the code because that could be at the end of a month or the start of a new one which changes which month it autofills.

And would you be so generous as to put descriptions above your lines of code? I dont know what all this is doing, but i want to learn!!

Sub MonthsDates()
   
   'What does this do?
   Dim Fnd As Range
   'What does this do?
   Dim Dys As Long
   'What does this do?
   Dim SDate As Date
   
   'What does this do?
   SDate = DateSerial(Year(Date), Month(Date), 1)
   'What does this do?
   Set Fnd = Columns(2).Find(Format(SDate, "dddd"), , , xlWhole, , , False, , False)
   'What does this do?
   Fnd.Offset(, 1).Value = SDate
   'What does this do?
   Dys = Day(WorksheetFunction.EoMonth(Date, 0))
   'What does this do?
   Fnd.Offset(, 1).AutoFill Fnd.Offset(, 1).Resize(Dys), xlFillDays

End Sub

 

[Fluff]

This will look in D2 of the active sheet for the first day of the month

Sub MonthsDates()
   
   Dim fnd As Range
   Dim Dys As Long
   Dim SDate As Date
   
   SDate = Range("D2").Value
'   looks for the sdate (formated as day of week) in col B & when found sets range Fnd to that cell
   Set fnd = Columns(2).Find(Format(SDate, "dddd"), , , xlWhole, , , False, , False)
'   offsets 1 col & inserts Sdate
   fnd.Offset(, 1).Value = SDate
'   enters the number of days in the month into Dys
   Dys = Day(WorksheetFunction.EoMonth(Date, 0))
'   autofills from the first day of the month to the last day
   fnd.Offset(, 1).AutoFill fnd.Offset(, 1).Resize(Dys), xlFillDays

End Sub

 

[wbstadeli]

Awesome, this works amazing!! I added a clear contents to my date ranges so that when i run the code again for a different month it starts with blank cells. One thing i need to fix on this is for some reason it enters 31 dates regardless of how long the month is. So for January that is fine, but February it enters all the dates of February and then adds 3/1/18, 3/2/18, and 3/3/18. Is there a way to stop the dates at the end of the month instead of end of 31 cells?

Sub MonthsDates()    
   Dim fnd As Range
   Dim Dys As Long
   Dim SDate As Date
   
   Range("C8:C49").Select
   Selection.ClearContents
   SDate = Range("D2").Value
'   looks for the sdate (formated as day of week) in col B & when found sets range Fnd to that cell
   Set fnd = Columns(2).Find(Format(SDate, "dddd"), , , xlWhole, , , False, , False)
'   offsets 1 col & inserts Sdate
   fnd.Offset(, 1).Value = SDate
'   enters the number of days in the month into Dys
   Dys = Day(WorksheetFunction.EoMonth(Date, 0))
'   autofills from the first day of the month to the last day
   fnd.Offset(, 1).AutoFill fnd.Offset(, 1).Resize(Dys), xlFillDays


End Sub

 

[Fluff]

Typo in the code it should be

Dys = Day(WorksheetFunction.EoMonth([COLOR=#ff0000]S[/COLOR]Date, 0))

 

[wbstadeli]

Thank you so much!! This is perfect!

 

[Fluff]

Glad to help & thanks for the feedback