Macro to populate all dates in a range

[Lath]

Hi,

I was wondering if you would please help me with a problem that I'm having.

I'm doing some work on a file relating to hotel bookings and want a macro or formula to populate all dates between a start and finish date so I can see exactly which nights they stayed.

I found a macro (below) that does a close approximation but i cant get it to loop down (which is essential as theres a significant number of guests) or populate all dates into one cell for each row rather than put each one into a new column (this would be preferable but not essential).

Sub WriteDates2()
Dim sc As Range

sd = Range("A2") ' start date
ed = Range("B2") ' end date
Set sc = Range("D2") ' start cell

' check dates

If ed - sd <= 0 Then Exit Sub
j = 0
For i = sd To ed
sc.Offset(0, j) = i
j = j + 1
Next i

End Sub​

Any help that you can give me in adapting this or creating a new one would be much appreciated. Please bear in mind that i have limited understanding of vba at the moment.

Many thanks in advance,

Paul

 

[Leith Ross]

Hello Paul,

It would help to see an example of the desired results.

Sincerely,
Leith Ross

 

[Lath]

Hello Leith,

I was trying to get D to populate based on A and B, something like this:

A B C D
Start Date Finish Date N/A Dates Between
10/02/2011 13/02/2011 10/02/2011, 11/02/2011, 12/02/2011
12/03/2011 13/03/2011 12/03/2011
10/02/2011 14/02/2011 10/02/2011, 11/02/2011, 12/02/2011, 13/02/2011


EDIT - Sorry, realised it didn't stay in tabular format.
Col A: Start date
Col B: Finish Date
Col C: N/A
Col D: Dates Between

Hopefully that clarifies things a little

 

[Haseeb Avarakkan]

Hello, Welcome to the board.

In C2 enter =A2, copy down...

In D2, copy across & down...

=IF(OR(C2="",C2=$B2-1),"",C2+1)

 

[Lath]

Hello Haseeb,

Thank you

A very neat solution, thanks for your help with with this.

Kind Regards,

Paul