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

wbstadeli

Board Regular
Joined
Mar 11, 2016
Messages
151
Office Version
  1. 365
Platform
  1. Windows
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:


Day of the week:Date:
Sunday
Monday1/1/2018
Tuesday
Wednesday
Thursday
Friday
Saturday
This table continues like above for 6 weeks...

<tbody>
</tbody>


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! :)
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
How about
Code:
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
 
Upvote 0
How about
Code:
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!! :)
Code:
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
 
Upvote 0
This will look in D2 of the active sheet for the first day of the month
Code:
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
 
Upvote 0
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?
Code:
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
 
Upvote 0
Typo in the code it should be
Code:
Dys = Day(WorksheetFunction.EoMonth([COLOR=#ff0000]S[/COLOR]Date, 0))
 
Upvote 0
Glad to help & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,315
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top