transposing and calculating
 

vB code to analyse list
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,
Scot

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

transposing and calculating
 

Some guidelines I might be able to give:

Look at column D and make a loop so that it writes every entry fo
everyday, example:


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

Dim SheetSize As Long 'Can be an Integer for speed issues but only if you are sure that it is not gonna be too long
Dim n As Long 'Same comment as above
Dim m As Integer 'Unless someone really likes a hospital and stays 32707 days in it, no Long is needed here
Dim k As Long 'Keep track of where we are
Dim i As Integer 'Count days
Dim sh As WorkSheet
Dim dt As String 'Month And Year


Sheets.Add 'Adds the new sheet where the entried for everyday will be added

ActiveSheet.Name = "Daily Entries"

Set sh = WorkBooks(WorkBook_Name).WorkSheets(WorkSheet_Name ) 'Substitute the name of workbook and sheet which the original entries are, where needed.

SheetSize = sh.Range("A1").End(xlDown).Row

'Now loop through the sheet
k = 1
For n = 1 To SheetSize
dt = Mid(sh.Cells(n, 2),Instr(sh.Cells(n, 2),"/"),Len(sh.Cells(n, 2)))
i = 0

'Now loop to write everyday from a entry

For m = k To k + sh.Cells(n, 4)
Cells(m, 1) = Cells(n, 1)
Cells(m, 2) = CStr(Mid(sh.Cells(n, 2),1,Instr(sh.Cells(n,2),"/") -1) + i) + dt
Cells(m, 5) = sh.Cells(n, 5)
i = i + 1
Next m
Next n

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


I think this should do it assuming no more than 30 days differenc
should appear as then the code will start writing days over 30 like 32
33 and so on since I did not handle months and year.

I wrote the code as I thought so I haven't tested it and knowing mysel
it is probably not bug free but I think it'll give you some guidelines
Beware I am no VBA guru and I am pretty sure this code can be writte
with less lines and much nicer.

It will write all the entries in a new worksheet.

Hope this helps.

Best Regards

Noo

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

transposing and calculating
 

Thank you very much for this. I will test the code and let you know ho
it goes !

Thanks again.
Scott

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