# Excel VBA Summary Report Solution Needed: Part 2

Part 1 at: http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_26417224.html

calacuccia's solution to Part 1 worked rather well and I'm grateful for his efforts.

Part 2:

In the updated Excel Workbook file (Attached: Report Solution1.wks) when running the MakeSummary routine:
When the Summary page encounters a large hourly number (Row 87 I87:65:31:32) we encounter a Runtime Error '13' Type Mismatch (see image).
On the solution in Part 1 we didn't have any large numbers so everything worked perfectly based on that example. Not so with the new larger hourly number.

Am I correct in assuming that these routines will accommodate reports that span as many months as I have data for? For example, will a 120 day report with 5000 lines need to be treated any differently?

As for the Timeline Sheet, it worked perfectly, but I have the following requests:
1. Computers be sorted alphabetically A - Z from Left to Right across the Top Row
2. The Time formatting is showing as a time AM vs. a Duration as it is in the Summary Sheet because the values are /24. I'd prefer to have the /24 so the cell formatting is standard between the Summary Sheet and the Timeline sheet if possible.

Thanks,
Brian E

Report-Solution1--071410-081910-.xls
runtimeerror13typemissm.jpg
###### Who is Participating?

Commented:
I really think it's an error to divide by 60 in column I.

how I see it:

Column i counts the seconds of all needed rows from column f (let's say no division by 60)
Column h = count of minutes from column e + seconds/60 to convert seconds into minutes decimal
Column g = count of hours from column d + minutes/60 to convert in hours decimal

If you divide by 60 in column i, and you divide this once again in column g in the formula
SUM(E2:E4)+I2/60

You'll have a division by 3600.

That's how I see it.
0

Commented:
When the Summary page encounters a large hourly number (Row 87 I87:65:31:32) we encounter a Runtime Error '13' Type Mismatch (see image).
On the solution in Part 1 we didn't have any large numbers so everything worked perfectly based on that example. Not so with the new larger hourly number
.
I changed the time conversion line to work for more than 24 hour formats>
ds.Cells(j, 4) = Left(ds.Cells(j, 3), InStr(1, ds.Cells(j, 3), ":") - 1)
ds.Cells(j, 5) = Mid(ds.Cells(j, 3), InStr(1, ds.Cells(j, 3), ":") + 1, 2)
ds.Cells(j, 6) = Right(ds.Cells(j, 3), 2)
By the way, I changed the seconds formula for column I in summary to this:
=SUM(F82:F82)
Before there was a division by 60 which was wrong in my opinion, don't know if I introduced the mistake or if you had it on your first example workbook>

Am I correct in assuming that these routines will accommodate reports that span as many months as I have data for? For example, will a 120 day report with 5000 lines need to be treated any differently?

It should not make any difference, the way it's written, but if it does please let us know
Part 3 in another comment>
0

Commented:
As for the Timeline Sheet, it worked perfectly, but I have the following requests:
1. Computers be sorted alphabetically A - Z from Left to Right across the Top Row
2. The Time formatting is showing as a time AM vs. a Duration as it is in the Summary Sheet because the values are /24. I'd prefer to have the /24 so the cell formatting is standard between the Summary Sheet and the Timeline sheet if possible.

I believe this solves it

The last line to sort alphabetically at the end of the routine (from left to right) and number format is everywhere [h]:mm. If the number format is still not right, I misunderstood.

Sub NewTimeLine()
Dim wb As Workbook
Dim ds As Worksheet, os As Worksheet
Dim lRow As Long, i As Long, j As Long, k As Long, tCounter As Long, rCounter As Long, nCol As Long
Dim cRange As Range
Set wb = ThisWorkbook
Set os = wb.Worksheets("Summary")
ds.Name = "Timeline"
ds.Cells(1, 1) = "TIMELINE"
ds.Cells(1, 1).Font.Bold = True
ds.Cells(1, 1).ColumnWidth = 12.57
lRow = os.Range("A65536").End(xlUp).Row
tCounter = 2
j = 2
For i = 2 To lRow
If os.Cells(i + 1, 1) <> os.Cells(i, 1) Then
ds.Cells(j, 1) = os.Cells(i, 1)
rCounter = tCounter
For k = rCounter To i
If os.Cells(k, 2) <> os.Cells(k + 1, 2) Or os.Cells(k, 1) <> os.Cells(k + 1, 1) Then
Set cRange = ds.Range("B1:IV1").Find(os.Cells(k, 2))
If Not cRange Is Nothing Then
ds.Cells(j, cRange.Column) = os.Cells(rCounter, 3)
ds.Cells(j, cRange.Column).NumberFormat = "[h]:mm"
Else
nCol = ds.Range("IV1").End(xlToLeft).Column + 1
ds.Cells(1, nCol) = os.Cells(k, 2)
ds.Cells(1, nCol).Font.Bold = True
ds.Cells(1, nCol).ColumnWidth = 11.29
ds.Cells(j, nCol) = os.Cells(rCounter, 3)
ds.Cells(j, nCol).NumberFormat = "[h]:mm"
End If
rCounter = k + 1
End If
Next k
j = j + 1
tCounter = i + 1

End If
Next i
ds.Cells(j, 1) = "Grand Totals"
For i = 2 To nCol
ds.Cells(j, i).Formula = "=Sum(" & ds.Cells(2, i).Address & ":" & ds.Cells(j - 1, i).Address & ")"
Next i
ds.Cells(j, nCol + 1).Formula = "=Sum(" & ds.Cells(j, 2).Address & ":" & ds.Cells(j, nCol).Address & ")"
ds.Range(ds.Cells(j, 1), ds.Cells(j, nCol + 1)).Font.Bold = True
ds.Range(ds.Cells(j, 1), ds.Cells(j, nCol + 1)).NumberFormat = "[h]:mm:ss;@"
ds.UsedRange.HorizontalAlignment = xlCenter
ds.Range(ds.Range("B1"), ds.Cells(j, nCol)).Sort Key1:=ds.Range(ds.Range("B1"), ds.Cells(1, nCol)), Order1:=xlAscending, Orientation:=xlLeftToRight
End Sub
0

Zone Technology ExpertAuthor Commented:
<>

I can see that the seconds conversion to minutes division by 60 was on my example workbook. I think this is correct when you have more than one row to find for how many minutes to add to the minute column. Where it is erroneous is when there is only a single entry. In that case it should omit the division by 60.

<>

The Alpha Sort worked, but the absolute Grand Totals at the bottom didn't follow the alpha sort. The total on column B is still for 1086.

The [h]:mm format applied is correct, but what it is applied to appears to determines if it is interpreted as time of day vs duration. On the Summary sheet, Duration in C2 is =G2/24 and can be calculated further if needed. Whereas, in the Timeline sheet, the formatted value returns 12:09:00 AM in the formula box. This may just be an aesthetics issue in the end as long as everything adds up correctly. On the UnattendedActivity sheet none of the duration formats allow any calculations on the fly using the mouse to highlight a data series and get an instant count or sum in the status bar. This is possible on both the Summary and Timeline Sheet so I can live with that.
0

Commented:
Changing the order of sorting and creating the formulas resolves the Alhpa sort issue with the Totals:

ds.Range(ds.Cells(j, 1), ds.Cells(j, nCol + 1)).Font.Bold = True
ds.Range(ds.Cells(j, 1), ds.Cells(j, nCol + 1)).NumberFormat = "[h]:mm:ss;@"
ds.UsedRange.HorizontalAlignment = xlCenter
ds.Range(ds.Range("B1"), ds.Cells(j, nCol)).Sort Key1:=ds.Range(ds.Range("B1"), ds.Cells(1, nCol)), Order1:=xlAscending, Orientation:=xlLeftToRight
ds.Cells(j, 1) = "Grand Totals"
For i = 2 To nCol
ds.Cells(j, i).Formula = "=Sum(" & ds.Cells(2, i).Address & ":" & ds.Cells(j - 1, i).Address & ")"
Next i
ds.Cells(j, nCol + 1).Formula = "=Sum(" & ds.Cells(j, 2).Address & ":" & ds.Cells(j, nCol).Address & ")"
End Sub

I'll have a look at your times, now I see what you mean.
0

Commented:
I have the impression the number format is exactly the same, only, as there is no formula linked, Excel chooses to interprete the value in the formula bar. I don't see immediately how this can be changed.

0

Zone Technology ExpertAuthor Commented:
OK, that's as good as it gets for the time formating. What to do about the division by /60 on singular seconds calcs? I don't suppose this margin of error is worth worrying about, but if a more precise method is available... your call on that I can live with it either way.
0

Zone Technology ExpertAuthor Commented:
You are right. I didn't realize your formulas were all singles and not groups like my original example. So yes, no division /60 on Column i - That will help - I'll make the edit and test...
0

Zone Technology ExpertAuthor Commented:
Spot on calacuccia - nice job - Thing of Beauty to see unfold - Thanks!

Final Code Snippet posted. Grade and points well earned. Appreciate the help.
``````Sub MakeSummary()
Dim wb As Workbook
Dim ds As Worksheet, os As Worksheet
Dim lRow As Long, i As Long, j As Long, fRecord As Long
Set wb = ThisWorkbook
Set os = wb.Worksheets("UnattendedActivity")
ds.Name = "Summary"
lRow = os.Range("A65536").End(xlUp).Row
os.Cells(7, 1).Copy ds.Cells(1, 1)
ds.Cells(1, 2).Value = os.Cells(7, 2).Value
ds.Cells(1, 3).Value = os.Cells(7, 9).Value
ds.Cells(1, 1).Copy
ds.Range("B1:C1").PasteSpecial xlPasteFormats
ds.Cells(1, 1).ColumnWidth = 15.14
ds.Cells(1, 2).ColumnWidth = 30
ds.Cells(1, 3).ColumnWidth = 10
ds.Cells(1, 4).ColumnWidth = 1.29
ds.Cells(1, 5).ColumnWidth = 2.29
ds.Cells(1, 6).ColumnWidth = 2.29
ds.Cells(1, 7).ColumnWidth = 4.43
ds.Cells(1, 8).ColumnWidth = 5.57
ds.Cells(1, 9).ColumnWidth = 7
j = lRow - 6
fRecord = j
os.Range("A8:A" & lRow).Copy
ds.Range("A2").PasteSpecial xlPasteValues
ds.Range("A2").PasteSpecial xlPasteFormats
os.Range("B8:B" & lRow).Copy
ds.Range("B2").PasteSpecial xlPasteValues
os.Range("I8:I" & lRow).Copy ds.Range("C2")
ds.Range("A2:C" & j).Sort Key1:=Range("A2"), Order1:=xlDescending, Key2:=Range("B2") _
, Order2:=xlDescending, Header:=xlNo, OrderCustom:=1, MatchCase:=False _
, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:= _
xlSortNormal

For i = lRow To 8 Step -1
ds.Cells(j, 4) = Left(ds.Cells(j, 3), InStr(1, ds.Cells(j, 3), ":") - 1)
ds.Cells(j, 5) = Mid(ds.Cells(j, 3), InStr(1, ds.Cells(j, 3), ":") + 1, 2)
ds.Cells(j, 6) = Right(ds.Cells(j, 3), 2)
If ds.Cells(j, 1) <> ds.Cells(j - 1, 1) Or ds.Cells(j, 2) <> ds.Cells(j - 1, 2) Then
ds.Cells(j, 9).Formula = "=SUM(F" & j & ":F" & fRecord & ")"
ds.Cells(j, 8).Formula = "=SUM(E" & j & ":E" & fRecord & ")+I" & j & "/60"
ds.Cells(j, 7).Formula = "=SUM(D" & j & ":D" & fRecord & ")+H" & j & "/60"
ds.Cells(j, 3).Formula = "=G" & j & "/24"
ds.Cells(j, 3).NumberFormat = "[h]:mm"
fRecord = j - 1
Else
ds.Range("A" & j).EntireRow.Hidden = True
ds.Cells(j, 3) = ""
End If
j = j - 1
Next i
ds.Range("D4:I4").EntireColumn.Hidden = True
End Sub

Sub NewTimeLine()
Dim wb As Workbook
Dim ds As Worksheet, os As Worksheet
Dim lRow As Long, i As Long, j As Long, k As Long, tCounter As Long, rCounter As Long, nCol As Long
Dim cRange As Range
Set wb = ThisWorkbook
Set os = wb.Worksheets("Summary")
ds.Name = "Timeline"
ds.Cells(1, 1) = "TIMELINE"
ds.Cells(1, 1).Font.Bold = True
ds.Cells(1, 1).ColumnWidth = 12.57
lRow = os.Range("A65536").End(xlUp).Row
tCounter = 2
j = 2
For i = 2 To lRow
If os.Cells(i + 1, 1) <> os.Cells(i, 1) Then
ds.Cells(j, 1) = os.Cells(i, 1)
rCounter = tCounter
For k = rCounter To i
If os.Cells(k, 2) <> os.Cells(k + 1, 2) Or os.Cells(k, 1) <> os.Cells(k + 1, 1) Then
Set cRange = ds.Range("B1:IV1").Find(os.Cells(k, 2))
If Not cRange Is Nothing Then
ds.Cells(j, cRange.Column) = os.Cells(rCounter, 3)
ds.Cells(j, cRange.Column).NumberFormat = "[h]:mm"
Else
nCol = ds.Range("IV1").End(xlToLeft).Column + 1
ds.Cells(1, nCol) = os.Cells(k, 2)
ds.Cells(1, nCol).Font.Bold = True
ds.Cells(1, nCol).ColumnWidth = 15
ds.Cells(j, nCol) = os.Cells(rCounter, 3)
ds.Cells(j, nCol).NumberFormat = "[h]:mm"
End If
rCounter = k + 1
End If
Next k
j = j + 1
tCounter = i + 1

End If
Next i
ds.Cells(j, 1) = "Grand Totals"
ds.Range(ds.Cells(j, 1), ds.Cells(j, nCol + 1)).Font.Bold = True
ds.Range(ds.Cells(j, 1), ds.Cells(j, nCol + 1)).NumberFormat = "[h]:mm:ss;@"
ds.UsedRange.HorizontalAlignment = xlCenter
ds.Range(ds.Range("B1"), ds.Cells(j, nCol)).Sort Key1:=ds.Range(ds.Range("B1"), ds.Cells(1, nCol)), Order1:=xlAscending, Orientation:=xlLeftToRight
ds.Cells(j, 1) = "Grand Totals"
For i = 2 To nCol
ds.Cells(j, i).Formula = "=Sum(" & ds.Cells(2, i).Address & ":" & ds.Cells(j - 1, i).Address & ")"
Next i
ds.Cells(j, nCol + 1).Formula = "=Sum(" & ds.Cells(j, 2).Address & ":" & ds.Cells(j, nCol).Address & ")"
ds.Cells.Columns.AutoFit
End Sub
``````
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.