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 day
difference, the days are shown as 1, and where 1 days difference, th
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 eac
unique reference number a line for each day they were in hospital an
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
counter, but I cant think how to go about writing it.
Any and every help appreciated !
Thanks,
Scott

--
Message posted from http://www.ExcelForum.com

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.

Try this :-


Code
-------------------

Sub test()
Dim FromSheet As Worksheet
Dim ToSheet As Worksheet
Dim FromRow As Long
Dim Torow As Long
'-
Dim FromDate As Date
Dim ToDate As Date
Dim Days As Integer
'-------------------------
Set FromSheet = ActiveSheet
FromRow = 2
Set ToSheet = Worksheets("Detail")
Torow = 2
While FromSheet.Cells(FromRow, 1).Value < ""
FromDate = FromSheet.Cells(FromRow, 2).Value
Days = FromSheet.Cells(FromRow, 4).Value
For d = 0 To Days
ToSheet.Cells(Torow, 1).Value = _
FromSheet.Cells(FromRow, 1).Value
ToSheet.Cells(Torow, 2).Value = FromDate + d
ToSheet.Cells(Torow, 3).Value = _
FromSheet.Cells(FromRow, 5).Value
Torow = Torow + 1
Next
FromRow = FromRow + 1
Wend
MsgBox ("Done")
End Sub

-------------------


--
Message posted from http://www.ExcelForum.com