Get all dates between 2 dates in vba

Shantanu_4612

New Member
Joined
Oct 3, 2016
Messages
27
I am a newbie in vba and I am trying to get in vba all dates between 2 dates, for example I will call the function with the parameters 01-01-2015 and 15-01-2015, and I will get in return an array with all the dates possibles, i.e :

This is the Data that I have;


<tbody>
[TD="class: xl71"]ID[/TD]
[TD="class: xl72, width: 73"]Start Date[/TD]
[TD="class: xl72, width: 73"]End Date[/TD]
[TD="class: xl73, width: 103"]Code[/TD]

[TD="class: xl68, align: right"]1234567[/TD]
[TD="class: xl69, align: right"]03-10-2016[/TD]
[TD="class: xl69, align: right"]15-10-2016[/TD]
[TD="class: xl70"]ABC_987654321[/TD]

[TD="class: xl65, align: right"]3456789[/TD]
[TD="class: xl66, align: right"]10-09-2016[/TD]
[TD="class: xl66, align: right"]20-09-2016[/TD]
[TD="class: xl67"]ABC_123456789[/TD]

</tbody>

The Result should be as below, and should stop when finds blanks in start date

[TABLE="width: 232"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Date[/TD]
[TD]Code[/TD]
[/TR]
[TR]
[TD="align: right"]1234567[/TD]
[TD="align: right"]03-10-2016[/TD]
[TD]ABC_987654321[/TD]
[/TR]
[TR]
[TD="align: right"]1234567[/TD]
[TD="align: right"]04-10-2016[/TD]
[TD]ABC_987654321[/TD]
[/TR]
[TR]
[TD="align: right"]1234567[/TD]
[TD="align: right"]05-10-2016[/TD]
[TD]ABC_987654321[/TD]
[/TR]
[TR]
[TD="align: right"]3456789[/TD]
[TD="align: right"]10-09-2016[/TD]
[TD]ABC_123456789[/TD]
[/TR]
[TR]
[TD="align: right"]3456789[/TD]
[TD="align: right"]11-09-2016[/TD]
[TD]ABC_123456789[/TD]
[/TR]
[TR]
[TD="align: right"]3456789[/TD]
[TD="align: right"]12-09-2016[/TD]
[TD]ABC_123456789[/TD]
[/TR]
[TR]
[TD="align: right"]3456789[/TD]
[TD="align: right"]13-09-2016[/TD]
[TD]ABC_123456789[/TD]
[/TR]
[TR]
[TD="align: right"]3456789[/TD]
[TD="align: right"]14-09-2016[/TD]
[TD]ABC_123456789[/TD]
[/TR]
[TR]
[TD="align: right"]3456789[/TD]
[TD="align: right"]15-09-2016[/TD]
[TD]ABC_123456789[/TD]
[/TR]
[TR]
[TD="align: right"]3456789[/TD]
[TD="align: right"]16-09-2016[/TD]
[TD]ABC_123456789[/TD]
[/TR]
[TR]
[TD="align: right"]3456789[/TD]
[TD="align: right"]17-09-2016[/TD]
[TD]ABC_123456789[/TD]
[/TR]
[TR]
[TD="align: right"]3456789[/TD]
[TD="align: right"]18-09-2016[/TD]
[TD]ABC_123456789[/TD]
[/TR]
[TR]
[TD="align: right"]3456789[/TD]
[TD="align: right"]19-09-2016[/TD]
[TD]ABC_123456789[/TD]
[/TR]
[TR]
[TD="align: right"]3456789[/TD]
[TD="align: right"]20-09-2016[/TD]
[TD]ABC_123456789[/TD]
[/TR]
</tbody>[/TABLE]

Please help me with this its a bit urgent, my job is on stake
 
Try this on a small sample to make sure you getting the right results on sheet "Raw".
NB:- If the results in "Raw" get over one million rows the results Move 3 columns across and starting again with row1.
I have tried this on 200K rows giving a result of approx. 2.4 million rows.
The code took about 3Minutes to run.
Code:
[COLOR="Navy"]Sub[/COLOR] MG05Oct57
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Dt [COLOR="Navy"]As[/COLOR] Date, ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] t
ac = 1
t = Timer
Application.ScreenUpdating = False
[COLOR="Navy"]With[/COLOR] Sheets("UX_Dump")
        [COLOR="Navy"]Set[/COLOR] Rng = .Range(.Range("BM2"), .Range("BM" & Rows.Count).End(xlUp))
[COLOR="Navy"]End[/COLOR] With
c = 1
[COLOR="Navy"]With[/COLOR] Sheets("Raw")
.Range("A1:C1").Value = Array("ID", "Date", "Code")
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]For[/COLOR] Dt = Dn.Value To Dn.Offset(, 2).Value
        c = c + 1
        [COLOR="Navy"]If[/COLOR] c >= 1000000 [COLOR="Navy"]Then[/COLOR] ac = ac + 3: c = 1
        .Cells(c, ac) = Dn.Offset(, -22).Value
        .Cells(c, ac + 1) = Dt
        .Cells(c, ac + 2) = Dn.Offset(, -2).Value
    [COLOR="Navy"]Next[/COLOR] Dt
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] With
Application.ScreenUpdating = True
MsgBox Timer - t
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick



Thanks a lot!!
This worked perfectly, with just a run time of 5 mins, even on our low configured computers and with a data of 5k entries.

Just one more question,
Can we convert a formula in a macro, n if yes I would love to know how,
As it will help me reduce the run time of all my templates
 
Upvote 0
Can you give me a script, a default script,
In which I can enter the cells from where the data has to be picked, where the output should reflect and the formula.
With a default setting of, when cell goes blank, macro should stop
 
Upvote 0

Forum statistics

Threads
1,226,772
Messages
6,192,928
Members
453,767
Latest member
922aloose

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
Back
Top