Results 1 to 10 of 10

Auto-fill fridays between 2 dates VBA

This is a discussion on Auto-fill fridays between 2 dates VBA within the Excel Questions forums, part of the Question Forums category; HI, I would like a user to type in a project start date, then a ptoject end date, and Excel ...

  1. #1
    Board Regular staticbob's Avatar
    Join Date
    Oct 2003
    Location
    Manchestershire
    Posts
    1,079

    Default Auto-fill fridays between 2 dates VBA

    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

  2. #2
    Board Regular amigos's Avatar
    Join Date
    Sep 2003
    Location
    London
    Posts
    407

    Default Re: Auto-fill fridays between 2 dates VBA

    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
    Amigos
    .: wisdom denotes the pursuing of the best ends by the best means :.

  3. #3
    Board Regular staticbob's Avatar
    Join Date
    Oct 2003
    Location
    Manchestershire
    Posts
    1,079

    Default Re: Auto-fill fridays between 2 dates VBA

    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

  4. #4
    Board Regular amigos's Avatar
    Join Date
    Sep 2003
    Location
    London
    Posts
    407

    Default Re: Auto-fill fridays between 2 dates VBA

    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
    Amigos
    .: wisdom denotes the pursuing of the best ends by the best means :.

  5. #5
    Board Regular staticbob's Avatar
    Join Date
    Oct 2003
    Location
    Manchestershire
    Posts
    1,079

    Default Re: Auto-fill fridays between 2 dates VBA

    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

  6. #6
    Board Regular amigos's Avatar
    Join Date
    Sep 2003
    Location
    London
    Posts
    407

    Default Re: Auto-fill fridays between 2 dates VBA

    Thanks,

    Well - everything is possible
    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?
    Amigos
    .: wisdom denotes the pursuing of the best ends by the best means :.

  7. #7
    Board Regular staticbob's Avatar
    Join Date
    Oct 2003
    Location
    Manchestershire
    Posts
    1,079

    Default Re: Auto-fill fridays between 2 dates VBA

    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

  8. #8
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454

    Default Re: Auto-fill fridays between 2 dates VBA

    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 ...

    ******** ******************** ************************************************************************>
    Microsoft Excel - Book2___Running: xl97 : OS = Windows 98
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    A
    B
    C
    D
    E
    F
    G
    H
    4
    StartDate01-Nov-03*WorkDate***WorkDate
    5
    EndDate30-Nov-03*01-Nov-03*FALSE*07-Nov-03
    6
    ***03-Nov-03***14-Nov-03
    7
    ***04-Nov-03***21-Nov-03
    8
    ***05-Nov-03***28-Nov-03
    9
    ***06-Nov-03****
    10
    ***07-Nov-03****
    11
    ***10-Nov-03****
    12
    ***11-Nov-03****
    13
    ***12-Nov-03****
    14
    ***13-Nov-03****
    15
    ***14-Nov-03****
    16
    ***17-Nov-03****
    17
    ***18-Nov-03****
    18
    ***19-Nov-03****
    19
    ***20-Nov-03****
    20
    ***21-Nov-03****
    21
    ***24-Nov-03****
    22
    ***25-Nov-03****
    23
    ***26-Nov-03****
    24
    ***27-Nov-03****
    25
    ***28-Nov-03****
    Sheet1*

    [HtmlMaker 2.20] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.


    I hope this helps. If I have misunderstood your question -- my apologies.
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  9. #9
    Board Regular staticbob's Avatar
    Join Date
    Oct 2003
    Location
    Manchestershire
    Posts
    1,079

    Default Re: Auto-fill fridays between 2 dates VBA

    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

  10. #10
    Board Regular amigos's Avatar
    Join Date
    Sep 2003
    Location
    London
    Posts
    407

    Default Re: Auto-fill fridays between 2 dates VBA

    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

    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
    Amigos
    .: wisdom denotes the pursuing of the best ends by the best means :.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com