VBA to manipulate dates based on cell value

Hi,

I have data in columns A-C and rows 1-5 representing holiday dates e.g 1st line signifies an arrival at Site 1 on 4/6/14 for 7 nights. Ultimately I want to show a duration of 1 night against every date that is covered by the booking...I have attempted to illustrate this below.


A B C
1 Site Date Duration
2 1 04/06/2014 7
3 2 03/06/2014 4
4 3 07/06/2014 1
5 4 01/06/2014 2

As stage 1, I have added VBA code that will add blank rows depending on the values in column C as follows:-

A B C
1 Site Date Duration
2 1 04/06/2014 7
3
4
5
6
7
8
9 2 03/06/2014 4
10
11
12
13 3 07/06/2014 1
14 4 01/06/2014 2
15

Ultimately I want the data to appear as below and am looking for some vba help to achieve this!


A B C
1 Site Date Duration
2 1 04/06/2014 1
3 1 05/06/2014 1
4 1 06/06/2014 1
5 1 07/06/2014 1
6 1 08/06/2014 1
7 1 09/06/2014 1
8 1 10/06/2014 1
9 2 03/06/2014 1
10 2 04/06/2014 1
11 2 05/06/2014 1
12 2 06/06/2014 1
13 3 07/06/2014 1
14 4 01/06/2014 1
15 4 02/06/2014 1



Many many thanks
Graham

Here you go. This will insert rows for you. Run the code on you first 4 row example:

Sub RunMe()
Dim ws As Worksheet:    Set ws = Sheets("Sheet1")
Dim lcell As Long, lDuration As Long, lDate As Long
Dim myDate As Date

For lcell = ws.Range("C" & Rows.Count).End(xlUp).Row To 2 Step -1
    lDuration = ws.Range("C" & lcell).Value - 1
    ws.Range("C" & lcell).Value = 1
    If lDuration <> 0 Then
        ws.Range("C" & lcell).Offset(1, 0).Resize(lDuration).EntireRow.Insert Shift:=xlDown
            For lDate = 1 To lDuration
                myDate = DateAdd("d", 1, ws.Range("B" & lcell).Offset(lDate - 1))
                ws.Range("A" & lcell).Offset(lDate, 0).Value = ws.Range("A" & lcell).Value
                ws.Range("B" & lcell).Offset(lDate, 0).Value = myDate
                ws.Range("C" & lcell).Offset(lDate, 0).Value = 1
            Next lDate
    End If
Next lcell

End Sub