VBA Help with looping date formula

[kast015]

I've tried performing this task with several combination of Excel formulas but to no avail. What I am trying to do is create a formula that loops through a START and a END dates. If the span contains a Sunday it will add one to the "Show Weeks."

If it does not have a Sunday in that span it will go to the next span until it finds a Sunday, it will continue to look at different spans until it finds the Sunday. Once it finds the Sunday it adds all the spans it went through and stores them into a variable (ShortWeek). Then divides by the ShortWeek variable divided by 1. So if there was three spans without a Sunday, each span would get .333 of a show week.

Here is some of the code that I attempted, I have two loops now. I think that I might need three loops not sure?

Sub Show_Week()
'1. DATE_SPAN = End Date through Start Date
'2. Loops through DATE_SPAN for every Sunday ++SHOW_WEEKS
    '2A. Loops until StartShow= EndShow
'3. If no Sundays in the current span, go the next span and look. Continue until it finds a Sunday
    '3A. '++SHORTWEEK, if Sunday is not found
    '3B. Once Sunday is found for "Short Weeks" restart with the first loop.
    
    Dim ShortWeek As Integer
    Dim ShowWeeks As Integer
    Dim StartShow As Integer
    Dim EndShow As Integer
    
    Application.ScreenUpdating = False


    Range("C4").Select


    Do Until ActiveCell.Value = ""
        StartShow = ActiveCell.Value
        EndShow = ActiveCell.Offset(0, 1).Value
        
       
            Do While StartShow <> EndShow
                StartShow = StartShow + 1
                If Weekday(StartShow, vbSunday) = vbSunday Then
                    ShowWeeks = ShowWeeks + 1
            Loop
       
    Loop


 
    Application.ScreenUpdating = True
End Sub

Would appreciate any insight on this! Thanks!

 

[jim may]

I can't wrap my mind around this right now, but I don't see the activecell address changing away from C4 for one thing...