VBA - Calculate interval between two dates

[OldAndWeak182]

Hi guys,
Would you be kind enough to help me out? I hit my head all day but finally gave up.
So...

first example:
In A1 I have 01/01/2021 and in A2 I have 05/01/2021 (european date).
In VBA how can I do to calculate the range "in between" and write for example in B1 01/01/2021, in B2 02/01/2021, in B3 03/01/2021 etc?

second example linked to the first:
Same scenario as before.
This time, however, I would like to insert variables: from B1 you must always write the range of dates but skip those that have been prescribed in another specific range X of cells, better still skip them based on the color of the cell of the X range (example 02/01/ 2021 is colored red so don't write it).

Good evening

 

[Marc L]

This was the Excel way …​

​

Now this is the classic algorithm 'useless' loopings cell by cell way (useless as non Excel) :​

Sub Demo0()
    Dim E&, L&, V, F%, R&
        E = 1
        L = [E1].End(xlDown).Row
    For V = [A1].Value To [A2].Value
            F = 1
        For E = E To L
            If Cells(E, 5).Value >= V Then
                If Cells(E, 5).Value = V Then If Cells(E, 5).Interior.Color = vbRed Then F = 0
                E = E + 1
                Exit For
            End If
        Next
        If F Then
            R = R + 1
            Cells(R, 2).Value = V
        End If
    Next
End Sub

​

 

[OldAndWeak182]

This was the Excel way …​

​

Now this is the classic algorithm 'useless' loopings cell by cell way (useless as non Excel) :​

Sub Demo0()
    Dim E&, L&, V, F%, R&
        E = 1
        L = [E1].End(xlDown).Row
    For V = [A1].Value To [A2].Value
            F = 1
        For E = E To L
            If Cells(E, 5).Value >= V Then
                If Cells(E, 5).Value = V Then If Cells(E, 5).Interior.Color = vbRed Then F = 0
                E = E + 1
                Exit For
            End If
        Next
        If F Then
            R = R + 1
            Cells(R, 2).Value = V
        End If
    Next
End Sub

​

what's the difference? the second is optimized better?

 

[OldAndWeak182]

This is what I am trying to achieve at the end of all experiments:
* I have a userform where I enter the data
* when I execute the code I have to write in the calendar the two variables indicated according to the date range, if I find one cell busy I jump to the next one, if I find it busy I jump to the last one, if I find it busy I return an error (referring to X,Y,Z)
* if the dates indicated are red then I skip the day and go to the next day.
Let me explain better with the example in the image: I insert from 01/04/2021 to 06/04/2021 but I write in the calendar only in the "Y" column.
Alone I was able to do everything only if the start and end date are the same, if instead I have a wider range I don't know how to proceed.
How would you set up the whole macro?

 

[Marc L]

what's the difference? the second is optimized better?

It was just a demonstration according to your post #2 attempt …​

My Demo0 well works with your sample but is not optimized for all cases​

where the codeline E = E + 1 can create some glitch so the revised version is​

Sub Demo0r()
    Dim E&, L&, V, F%, R&
        E = 1
        L = [E1].End(xlDown).Row
    For V = [A1].Value To [A2].Value
            F = 1
        For E = E To L
            If Cells(E, 5).Value >= V Then
                If Cells(E, 5).Value = V Then
                    If Cells(E, 5).Interior.Color = vbRed Then F = 0
                    E = E + 1
                End If
                    Exit For
            End If
        Next
        If F Then
            R = R + 1
            Cells(R, 2).Value = V
        End If
    Next
End Sub

 

[Marc L]

How would you set up the whole macro?

Elaborate what the code should do …​

 

[OldAndWeak182]

Elaborate what the code should do …​

Everything I wrote, I set a loooong macro to do the whole thing so I would like to read and learn how someone who is really capable to write a code do the task

 

[OldAndWeak182]

the most immediate thing I have to do:

            If Cells(E, 5).Value >= V Then
                If Cells(E, 5).Value = V Then
                    If Cells(E, 5).Interior.Color = vbRed Then F = 0
                    E = E + 1
                End If

I need to modify this step so that the dates to be excluded are on many different ranges. Should a cycle be set? how?

 

[Marc L]

​

Or load first those dates in a single array in order to use it to compare …​

 

[OldAndWeak182]

This was the Excel way …​

​

Now this is the classic algorithm 'useless' loopings cell by cell way (useless as non Excel) :​

Sub Demo0()
    Dim E&, L&, V, F%, R&
        E = 1
        L = [E1].End(xlDown).Row
    For V = [A1].Value To [A2].Value
            F = 1
        For E = E To L
            If Cells(E, 5).Value >= V Then
                If Cells(E, 5).Value = V Then If Cells(E, 5).Interior.Color = vbRed Then F = 0
                E = E + 1
                Exit For
            End If
        Next
        If F Then
            R = R + 1
            Cells(R, 2).Value = V
        End If
    Next
End Sub

​

For some reason I have issue with formatting cells as dates.
Is possible to do the the whole things formatting cells as text?

 

[Marc L]

​

For some reason it should be difficult as a text can't be a date (numeric) so it depends on if Excel can understand the text as a valid date.​

​

But as formatting a cell is at kid level so the easier is to directly work with dates rather than texts …​