Log File CleanUp

[hstockbridge5]

Hi,

I have been asked to prepare a report from a log file (.xls) that
reports 34 User activities. The current time and date values are
formatted like this...

LineNo User Activity Value
2 DoeJ sessionSTART at 10:46:14
3 DoeJ sessionDATE 09-07-2004
38 DoeJ sessionENDtime 10:51:44
40 DoeJ sessionSTART at 10:56:07
41 DoeJ sessionDATE 09-07-2004
76 DoeJ sessionENDtime 10:56:24
77

... but I would like to report them like this ...


LineNo User Activity Value
2 DoeJ sessionStart at 09-07-2004 10:46:14
38 DoeJ sessionEnd at 09-07-2004 10:51:44
40 DoeJ sessionStart at 09-07-2004 10:56:07
76 DoeJ sessionEnd at 09-07-2004 10:56:24
Any help you can offer would be appreciated.

- Henry

 

[Guest]

Henry,

Assuming your log file has a tab called Log where the data is contained,
and the first row in that tab is the headers, this should work. If not,
please substitute the correct worksheet and cell references for the process
to start.

Sub Combine_Log()
Worksheets("Log").Activate
Cells(1, 1).Select
ActiveCell.CurrentRegion.Name = "mylog"
Range("mylog").Cells(2, 1).Select
Do While ActiveCell.Value <> xlnull
ActiveCell.Offset(0, 3).Value = ActiveCell.Offset(1, 3).Value _
+ ActiveCell.Offset(0, 3).Value
ActiveCell.Offset(2, 3).Value = ActiveCell.Offset(2, 3).Value _
+ ActiveCell.Offset(0, 3).Value
ActiveCell.Offset(1, 0).EntireRow.Delete
ActiveCell.Offset(2, 0).Select
Loop
Range("mylog").Cells(1, 4).EntireColumn.NumberFormat = "[$-409]mm-dd-yy
h:mm:ss"
End Sub