Solved

Excel VBA Summary Report Solution Needed: Part 2

Posted on 2010-08-20
9
254 Views
Last Modified: 2012-05-10
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
0
Comment
Question by:BrianEsser
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 17

Expert Comment

by:calacuccia
ID: 33489115
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
 
LVL 17

Expert Comment

by:calacuccia
ID: 33489168
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")
Set ds = wb.Worksheets.Add(After:=os)
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
 

Author Comment

by:BrianEsser
ID: 33489449
<>

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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 17

Expert Comment

by:calacuccia
ID: 33489497
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
 
LVL 17

Expert Comment

by:calacuccia
ID: 33489541
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
 

Author Comment

by:BrianEsser
ID: 33489570
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
 
LVL 17

Accepted Solution

by:
calacuccia earned 500 total points
ID: 33489608
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
 

Author Comment

by:BrianEsser
ID: 33489675
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
 

Author Comment

by:BrianEsser
ID: 33489725
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")
Set ds = wb.Worksheets.Add(After:=os)
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")
Set ds = wb.Worksheets.Add(After:=os)
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

Open in new window

0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

724 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question