One way:

Public Sub ExpandRecords()
Dim vSource As Variant
Dim rDest As Range
Dim i As Long
Dim j As Long

vSource = Sheets("Sheet1").Range("A1:E" & _
Range("A" & Rows.Count).End(xlUp).Row).Value
Set rDest = Sheets("Sheet2").Range("A1:C1")
ReDim vDest(1 To UBound(vSource, 1), 1 To 3)
For i = 1 To UBound(vSource, 1)
For j = 0 To (vSource(i, 4))
rDest(1) = Application.Substitute(vSource(i, 1), "E", "H")
rDest(2) = CDate(vSource(i, 2) + j)
rDest(3) = vSource(i, 5)
Set rDest = rDest.Offset(1, 0)
Next j
Next i
End Sub


In article ,
scottwilsonx wrote:

I hope that someone can help with this question:
I have data in an Excel worksheet. 5 columns:
column A has a unique reference number
column B has the start date (admission date to the hospital)
column C has the end date (discharge date from the hospital)
column D has the days inbetween column B and C (where 0 days
difference, the days are shown as 1, and where 1 days difference, the
days are shown as 2, etc).
column E has a code attached, eg: E40 which relates to a product.

I want to run a vBA code agains the list so that it will show, for each
unique reference number a line for each day they were in hospital and
will also record the code in column E.

eg: If the line shows: E213123 4/5/2004 6/5/2004 2 E40 :
H213123 4/5/2004 E40
H213123 5/5/2004 E40
H213123 6/5/2004 E40

Hope someone can help me with this code, which I know should include a
counter, but I cant think how to go about writing it.
Any and every help appreciated !
Thanks,
Scott.