Holidays Between 2 Dates

I found a bit of vb code that’s close to what I need here:

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_23195737.html

I’ve listed it below but I’d like change it so that instead of returning a list dates in between the start and end dates, I want to return the list of holidays between the start and ends dates. I have a list of holiday dates in Row Y.

Private Sub CommandButton1_Click()

    startdate = Range("F11").Value
    enddate = Range("G11").Value
    daysbetween = enddate - startdate
    Results = "Day 1" & vbTab & startdate & vbCrLf
    For i = 1 To daysbetween
        nextdate = startdate + i
        Results = Results & "Day " & i & vbTab & nextdate & vbCrLf
    Next i
    MsgBox Results
End Sub

Open in new window

LVL 1
tracymsAsked:
Who is Participating?
 
Saqib Husain, SyedConnect With a Mentor EngineerCommented:
Put your cursor on one of the start dates or end dates and then run this macro

Private Sub CommandButton1_Click()

    startdate = ActiveCell.EntireRow.Cells(1).Value
    enddate = ActiveCell.EntireRow.Cells(2).Value
   
    For Each cel In Range("i2:i" & Range("i" & Rows.Count).End(xlUp).Row)
        If cel.Value >= startdate And cel.Value <= enddate Then
            Results = Results & "Day " & cel.Value - startdate & vbTab & cel & vbCrLf
        End If
    Next cel
   
    MsgBox Results
End Sub
0
 
Saqib Husain, SyedEngineerCommented:
Try this loop in your code. I have not tested it. If you have a problem then post your file and I can try to solve it.


for each cel in range("y1:y"& range("y" & rows.count).end(xlup).row)
if cel.value >= startdate and cel.value <= enddate then
 Results = Results & "Day " & cel.value - startdate & vbTab & nextdate & vbCrLf
endif
next cel
0
 
tracymsAuthor Commented:
I moved my holidays to row ak, starting in row ak2. I used the code below but it only shows the start date:


Private Sub CommandButton1_Click()

    startdate = Range("F11").Value
    enddate = Range("G11").Value
    daysbetween = enddate - startdate
    Results = "Day 1" & vbTab & startdate & vbCrLf
   
   
    For Each cel In Range("ak2:ak" & Range("ak" & Rows.Count).End(xlUp).Row)
If cel.Value >= startdate And cel.Value <= enddate Then
 Results = Results & "Day " & cel.Value - startdate & vbTab & nextdate & vbCrLf
End If
Next cel
   
   
   
    MsgBox Results
End Sub
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Saqib Husain, SyedEngineerCommented:
Can you upload a small sample excel file for testing?
0
 
tracymsAuthor Commented:
I've attached a test file.
holidays.xlsm
0
 
tracymsAuthor Commented:
ssaqibh,

I've changed my request - if that's okay. Please see attached spreadsheet for details.
holidaysRevisited.xlsm
0
 
Saqib Husain, SyedEngineerCommented:
This new request is significantly differently from the original request.

If the original request is appropriately answered the you should close this question and open another with the new request.

If there is something lacking in the solution with respect to the original request then continue to post here.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.