VBA - Calculate interval between two dates

OldAndWeak182

New Member
Joined
Apr 29, 2021
Messages
23
Office Version
  1. 365
Platform
  1. Windows
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 :coffee:
 

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
1,765
Office Version
  1. 2010
Platform
  1. Windows
This was the Excel way …​
Now this is the classic algorithm 'useless' loopings cell by cell way (useless as non Excel) :​
VBA Code:
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
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

OldAndWeak182

New Member
Joined
Apr 29, 2021
Messages
23
Office Version
  1. 365
Platform
  1. Windows
This was the Excel way …​
Now this is the classic algorithm 'useless' loopings cell by cell way (useless as non Excel) :​
VBA Code:
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

New Member
Joined
Apr 29, 2021
Messages
23
Office Version
  1. 365
Platform
  1. Windows
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?

try3.jpg
 

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
1,765
Office Version
  1. 2010
Platform
  1. Windows
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​
VBA Code:
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
 

OldAndWeak182

New Member
Joined
Apr 29, 2021
Messages
23
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Apr 29, 2021
Messages
23
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

the most immediate thing I have to do:

VBA Code:
            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

Well-known Member
Joined
Apr 5, 2021
Messages
1,765
Office Version
  1. 2010
Platform
  1. Windows
Or load first those dates in a single array in order to use it to compare …​
 

OldAndWeak182

New Member
Joined
Apr 29, 2021
Messages
23
Office Version
  1. 365
Platform
  1. Windows
This was the Excel way …​
Now this is the classic algorithm 'useless' loopings cell by cell way (useless as non Excel) :​
VBA Code:
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

Well-known Member
Joined
Apr 5, 2021
Messages
1,765
Office Version
  1. 2010
Platform
  1. Windows
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 …​
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,968
Messages
5,767,375
Members
425,409
Latest member
Whatisanexcel

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
Top