CODEWITHSUNDEEP
vbaexcel
CeFu
CeFu

Reputation: 149

Not correctly summing column between all sheets in Workbook

Backstory: I'm pulling off phone records off a PBX server. I'm adjusting & calculating long distance calls. I have already filtered out local & toll free numbers. I split the workbook into sheets by tenant's name. My macro below is formatting & calculating all sums for duration & total cost of that call.

The issue: My macro is not selecting all the phone calls rows correctly (I beleive). It is able to correctly calculate the first sheet (I select all phone call rows), but when it moves to the others, it is unable to correctly sum them all.

Exact Error sheet: 

Start Time  Duration    Calling Name    Dialed Number    Cost 
6/1/2016 15:07  0:30:55 BLANK_I380            NUMBER     $3.72 
6/3/2016 12:26  0:05:40 BLANK_I380            NUMBER     $0.72 
6/6/2016 13:49  0:00:08 BLANK_I380            NUMBER     $0.12 
6/6/2016 13:50  0:00:08 BLANK_I380            NUMBER     $0.12 
6/6/2016 13:51  0:01:15 BLANK_I380            NUMBER     $0.12 
6/16/2016 8:29  0:01:42 BLANK_I380            NUMBER     $0.24 
Total Duration: 0:02:50                       Total Cost:    $0.72

First Correct sheet

Start Time  Duration    Calling Name    Dialed Number    Cost 
6/1/2016 9:20   0:00:09 BLANK Shining_I113  1313600000   $0.12 
6/1/2016 9:25   0:00:22 BLANK Shining_I113  1248600000   $0.12 
6/1/2016 9:26   0:00:54 BLANK Shining_I113  1248600000   $0.12 
Total Duration: 0:01:25                                 Total Cost:  $0.36

Code

Sub FormatEntry()
Dim TotalCost As Double
Dim TotalTime As Double

For Each ws In ActiveWorkbook.Worksheets
             On Error Resume Next 'Will continue if an error results
    ws.Range("E:E").NumberFormat = "_-[$$-40B]* #,##0.00_ ;_-[$$-40B]* -#,##0.00 ;_-[$$-40B]* ""-""??_ ;_-@_ "
    ws.Range("A1").End(xlDown).Offset(1).Font.Bold = True
    ws.Range("A1").End(xlDown).Offset(1).Value = "Total Duration:"
    ws.Range("D1").End(xlDown).Offset(1).Font.Bold = True
    ws.Range("D1").End(xlDown).Offset(1).Value = "Total Cost:"
     ws.Range("E2").End(xlDown).Offset(1, 0).Value = _
     WorksheetFunction.Sum(Range("E2:E" & Cells.SpecialCells(xlLastCell).Row))
     ws.Range("B2").End(xlDown).Offset(1, 0).Value = _
     Format(WorksheetFunction.Sum(Range("B2:B" & Cells.SpecialCells(xlLastCell).Row)), "hh:mm:ss")
Next ws

End Sub

Upvotes: 0

Views: 33

Answers (1)

BruceWayne
BruceWayne

Reputation: 23283

It could be due to your Range() use in the Sum functions - it doesn't reference a worksheet, so can cause issues. Try this:

Sub FormatEntry()
Dim TotalCost As Double
Dim TotalTime As Double

For Each ws In ActiveWorkbook.Worksheets
    On Error Resume Next     'Will continue if an error results
    With ws
        .Range("E:E").NumberFormat = "_-[$$-40B]* #,##0.00_ ;_-[$$-40B]* -#,##0.00 ;_-[$$-40B]* ""-""??_ ;_-@_ "
        .Range("A1").End(xlDown).Offset(1).Font.Bold = True
        .Range("A1").End(xlDown).Offset(1).Value = "Total Duration:"
        .Range("D1").End(xlDown).Offset(1).Font.Bold = True
        .Range("D1").End(xlDown).Offset(1).Value = "Total Cost:"
        .Range("E2").End(xlDown).Offset(1, 0).Value = _
        WorksheetFunction.Sum(.Range("E2:E" & .Cells.SpecialCells(xlLastCell).Row))
        .Range("B2").End(xlDown).Offset(1, 0).Value = _
        Format(WorksheetFunction.Sum(.Range("B2:B" & .Cells.SpecialCells(xlLastCell).Row)), "hh:mm:ss")
    End With
Next ws
End Sub

The main idea is that any time you have Range(), Cells(), Columns(), Rows(), etc. you want to explicitly state the worksheet that you expect the Range/Cells/Columns to be on. Otherwise, as you've discovered, VBA can return some unexpected results.

Upvotes: 1

Related Questions