Populate Dates in different cells between date range - VBA

I am stuck at a point where I have a date range and need to populate the dates between that date range in different cells.

E.g.: A1 shows 04/01/2015 and B1 shows 04/05/2015. Result should be starting from C1 to populate 04/01/2015, C2 as 04/02/2015, C3 as 04/03/2015 and so on..

The date range can go upto 100 days and to repeat this activity until the last row with date of the sheet.

Any help will be much appreciated. I have attached a spreadsheet for your reference and workings.

Cheers
RP

Re: Populate Dates in different cells between date range - VBA

Admin <> can u pls delete this post.. I guess I posted incomplete request

Thanks

Re: Populate Dates in different cells between date range - VBA

Thanks for the response.

Re: Populate Dates in different cells between date range - VBA

The request was incomplete.. Since you have already provided the response, just want to check one more thing.. Can I get the similar result for multiple line items (like Vlookup way).. Below is what I am actually trying to achieve (spread sheet attached)..

Data:
[TABLE="width: 565"]

Employee ID

Product

From

To

[TD="align: right"]11709
[/TD]

Apple

[TD="align: right"]4/3/2015
[/TD]
[TD="align: right"]4/5/2015
[/TD]

[TD="align: right"]12094
[/TD]

Orange

[TD="align: right"]4/15/2015
[/TD]
[TD="align: right"]4/16/2015
[/TD]

[TD="align: right"]11709
[/TD]

Apple

[TD="align: right"]4/29/2015
[/TD]
[TD="align: right"]4/30/2015
[/TD]

[TD="align: right"]12094
[/TD]

Orange

[TD="align: right"]4/18/2015
[/TD]
[TD="align: right"]4/20/2015
[/TD]

Result

[TD="align: right"]11709
[/TD]

Apple

[TD="align: right"]3-Apr
[/TD]
[TD="align: right"]4-Apr
[/TD]
[TD="align: right"]5-Apr
[/TD]
[TD="align: right"]29-Apr
[/TD]
[TD="align: right"]30-Apr
[/TD]
[TD="align: right"][/TD]

[TD="align: right"]12094
[/TD]

Orange

[TD="align: right"]15-Apr
[/TD]
[TD="align: right"]16-Apr
[/TD]
[TD="align: right"]18-Apr
[/TD]
[TD="align: right"]19-Apr
[/TD]
[TD="align: right"]20-Apr
[/TD]

[/TABLE]

Re: Populate Dates in different cells between date range - VBA

When I go to the below step, I am getting Type mismatch error..

x = DateDiff("d", a(i, 3), a(i, 4)) + 1

Re: Populate Dates in different cells between date range - VBA

When I am trying to repeat the same activity until the last active cell of column B using below code, I am getting result only for first row and then it just loop without populating any result from row 2.

Sub test()
 Dim x
 Dim A As Integer
 numrows = Range("B1", Range("B1").End(xlDown)).Rows.Count
 Range("c1").Select
 For A = 1 To numrows
  x = DateDiff("d", [a1], [b1]) + 1 
    With Range("c1") 
        [a1].Copy .Cells 
        .AutoFill .Resize(, x) 
    End With 
    Next
End Sub

Re: Populate Dates in different cells between date range - VBA

I have attached the spreadsheet with what result I am getting with code below and what I am expecting..
# of rows is not limited and can very each time. Sorry for the confusion..

Sub test() 
    Dim x 
    Dim A As Integer 
    numrows = Range("B1", Range("B1").End(xlDown)).Rows.Count 
    Range("c1").Select 
    For A = 1 To numrows 
        x = DateDiff("d", [a1], [b1]) + 1 
        With Range("c1") 
            [a1].Copy .Cells 
            .AutoFill .Resize(, x) 
        End With 
    Next 
End Sub

Re: Populate Dates in different cells between date range - VBA

Thanks.. Awesome that works for me, but got stuck with one more last thing..

If the dates are same in cell a1 and b1, I should be getting the first date but it is giving me runtime error. Attached spreadsheet with the code..

Re: Populate Dates in different cells between date range - VBA

Thank you!!

Re: Populate Dates in different cells between date range - VBA

How do I eliminate weekends from the summary that shows up in sheet2. E.g.: in attached sheet I got date range from 16-Nov to 23-Nov for all Type. When I run the macro in sheet2 I am getting 16-Nov, 17-Nov, 18-Nov, 19-Nov, 20-Nov, 21-Nov, 22-Nov, 23-Nov

I do no need weekends and but should show only 16-Nov, 17-Nov, 18-Nov, 19-Nov, 20-Nov, 23-Nov

Dim a, i As Long, ii As Long, w, DD, ub As Long
a = Cells(1).CurrentRegion.Value
    With CreateObject("Scripting.Dictionary")
        For i = 2 To UBound(a, 1)
            If Not .exists(a(i, 1)) Then
                ReDim w(1 To 2)
                For ii = 1 To 2
                    w(ii) = a(i, ii)
                Next
                .Item(a(i, 1)) = w
            Else
                w = .Item(a(i, 1))
            End If
            ub = UBound(w)
            DD = DateDiff("d", a(i, 3), a(i, 4)) + 1
            ReDim Preserve w(1 To UBound(w) + DD)
            For ii = ub + 1 To UBound(w)
                w(ii) = a(i, 3) + ii - ub - 1
            Next
            .Item(a(i, 1)) = w
        Next
        For i = 0 To .Count - 1
Sheets("Sheet2").Select
Range("A1").Select
    
Cells(i + 1, "a").Resize(, UBound(.items()(i))) = .items()(i)
 Next
    End With
    With [e2].CurrentRegion
        .Offset(, 2).NumberFormat = "d-mmm"
        .Columns.AutoFit
    End With
[B][/B]