Auto-fill fridays between 2 dates VBA

staticbob

Well-known Member
Joined
Oct 7, 2003
Messages
1,079
HI,

I would like a user to type in a project start date, then a ptoject end date, and Excel give me all the Fridays between (and including if ness.) those 2 dates.

I know how to do the msgboxs, I just need the code that will fill-in the days starting from cell A2 on sheet2.

Thanks in advance.

Bob
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Try this code, it will insert in column A all Fridays from between DayStart and DayEnd.

Sub Fridays()

Dim DayStart As Date
Dim DayEnd As Date
Dim DayCurr As Date
Dim i As Integer
Dim DateDif As Integer

DayStart = #11/1/2003# ' change start date in here
DayEnd = #11/30/2003# ' change end date in here

DateDif = DayEnd - DayStart

For i = 0 To DateDif
DayCurr = DayStart + i

If Weekday(DayCurr, vbMonday) = 5 Then
ActiveSheet.Cells(Application.WorksheetFunction.CountA(ActiveSheet.Columns(1)) + 1, 1).Value = Format(DayCurr, "dd mmm yy")
End If

Next i


End Sub
 
Upvote 0
Thanks, but how do I get these dates inserted on a different sheet, sheet 2 for example ??

Also, is it possible to have the user select the dates from a drop-down calander rather than just typing in an input box ???


Cheers
 
Upvote 0
simply change ActiveSheet.Cells(Application.WorksheetFunction.CountA(ActiveSheet.Columns(1)) + 1, 1).Value = Format(DayCurr, "dd mmm yy") part into


Sheets(2).Cells(Application.WorksheetFunction.CountA(Sheets(2).Columns(1)) + 1, 1).Value = Format(DayCurr, "dd mmm yy")

- for Sheet2 etc.

HTH
 
Upvote 0
Thanks Amigos, very helpful for us newbies as always.

I have this working with inout boxes to obtain the dates, but is it possible to have calendar style select boxes so the user can just select the date required ? Or am I being too ambitious ?!?

Thanks
 
Upvote 0
Thanks,

Well - everything is possible :biggrin:
but I don't think you should use form (more coding required) - what about taking 2 cells, and making lists of dates in them? - use data/validation/allow: List/Source: Create list of all days in period you need somewhere in your spreadsheed and input in here. Than you could have Button runing this macro and taking Start and End from those cells. What do you think?
 
Upvote 0
Yeah, thanks Amigos, I had thought of that but I'm really fussy ! The users want it looking nice.

I have just found an old post from PaddyD using a Calendar control and it look lovely, I've even got is only showing when the specific cell is clicked !

Sweet.

Problem is, with my lack of VBA fundamentals, I can't can't get this multiple if working . . .

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, [B4]) Is Nothing Then
Calendar1.Visible = True
Else: Calendar1.Visible = False
End If
If Not Intersect(Target, [B5]) Is Nothing Then
Calendar2.Visible = True
Else: Calendar2.Visible = False
End Sub

Thanks again.
Bob
 
Upvote 0
Hi staticbob:

In line with what Amigos has recommended, how about using the StartDate and EndDate to create a series of weekday WorkDates as in D4:D34, and then using Advanced Filter to extract only Fridays using the criterion as shown in F4:F5 -- see the following illustration ...
Book2
ABCDEFGH
4StartDate01-Nov-03WorkDateWorkDate
5EndDate30-Nov-0301-Nov-03FALSE07-Nov-03
603-Nov-0314-Nov-03
704-Nov-0321-Nov-03
805-Nov-0328-Nov-03
906-Nov-03
1007-Nov-03
1110-Nov-03
1211-Nov-03
1312-Nov-03
1413-Nov-03
1514-Nov-03
1617-Nov-03
1718-Nov-03
1819-Nov-03
1920-Nov-03
2021-Nov-03
2124-Nov-03
2225-Nov-03
2326-Nov-03
2427-Nov-03
2528-Nov-03
Sheet1


I hope this helps. If I have misunderstood your question -- my apologies.
 
Upvote 0
Thanks Yogi,

That would work fine, but now I've found the Calendar control I much prefer it !

See this post for info . .
http://www.mrexcel.com/board2/viewtopic.php?t=60316

I am just now strugglin to hide this until the relevant cells are clicked. Then, how would I get the original code that Amigos posted to call the date from cell B4 for start date, and B5 for end date. Then I can write all the fridays between the 2 to a new sheet !!!

Thanks
 
Upvote 0
try this code,
it inputs dates (clicked on Calendar form) to selected cells on sheet and you can use them afterwards for "start" and "end". If you select cells other than B1, B2 or more than 1 cell it will give you error message
Well, there is also one more problem - if End < Start, tell me if you want to handle this as well or you trust your workmates :biggrin:

Private Sub Calendar1_Click()

If Selection.Count <> 1 Then
WrongSelection
ElseIf Selection.Address <> ActiveSheet.Cells(1, 2).Address And Selection.Address <> ActiveSheet.Cells(2, 2).Address Then
WrongSelection
Else
Selection = Calendar1.Value
End If
End Sub


Function WrongSelection()
MsgBox "Wrong selection" & Chr(10) & "You have to select single Cell B1 or B2"
End Function


HTH
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,541
Latest member
iparraguirre89

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