Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Transposing | Excel Discussion (Misc queries) | |||
need help with transposing | Excel Discussion (Misc queries) | |||
Transposing | Excel Discussion (Misc queries) | |||
Transposing | Excel Worksheet Functions | |||
TRANSPOSING | Excel Discussion (Misc queries) |