Solved

Excel VBA Summary Report Solution Needed: Part 2

Posted on 2010-08-20
9
245 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
  • 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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

760 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now