frank_m
02-07-2012, 11:00 PM
The Function Sum_Visible_Cells seems to be rounding the cell values before giving me a sum total in a msgbox
See attached workbook where the Actual sum of all the totals for December is: 303,284.39
but the Sum totaled using code is: 303,281.00
Would someone be so kind as to point out why that is happening?
Thanks
Private Sub CommandButton1_Click()
'modified slightly Code found at this link
'http://www.dailydoseofexcel.com/archives/2008/11/26/autofiltering-on-months/
Dim lMonth As Long, lYear As Long, rngCell As Range, rngCol As Range
Dim LastRow As Long, wks As Worksheet
Dim DateCell As Range, rng As Range, x As Long, LValue As String
Set wks = ActiveSheet
With wks
LastRow = ActiveSheet.Range("D" & Rows.Count).End(xlUp).Row
If ActiveCell.Row = 1 Or ActiveCell.Row > LastRow Then
MsgBox "Please select a row that has data"
Exit Sub
End If
Set rngCol = ActiveSheet.Range("D2:D" & LastRow) ' Cost Total Column
Set rngCell = Range("A2:A" & LastRow) ' Date Column
Set DateCell = ActiveCell.EntireRow.Cells(1)
DateCell.Select
If Not rngCell Is Nothing Then
If IsDate(DateCell.Value) Then
lMonth = Month(DateCell.Value)
lYear = Year(DateCell.Value)
'Check if there is an autofilter
If rngCell.Parent.AutoFilterMode Then
'Make sure DateCell is within autofilter range
If Not Intersect(DateCell, _
rngCell.Parent.AutoFilter.Range) Is Nothing Then
'Create filter
With rngCell.Parent.AutoFilter
.Range.AutoFilter DateCell.Column - .Range(1).Column + 1, _
">=" & DateSerial(lYear, lMonth, 1), _
xlAnd, _
"<=" & DateSerial(lYear, lMonth + 1, 0)
End With
End If
End If
End If
End If
x = Sum_Visible_Cells(rngCol)
LValue = Format(x, "Currency")
MsgBox LValue
Set rng = .Range(.Cells(1, 1), .Cells(LastRow, 4))
If Not rng Is Nothing Then
'turn filter off
.AutoFilterMode = False
'reset filter
rng.AutoFilter
.EnableAutoFilter = True
End If
End With
End Sub
Function Sum_Visible_Cells(Cells_To_Sum As Object)
Dim aCell As Range
Dim total As Long
Application.Volatile
For Each aCell In Cells_To_Sum
If aCell.Rows.Hidden = False Then
If aCell.Columns.Hidden = False Then
total = total + aCell.Value
End If
End If
Next
Sum_Visible_Cells = total
End Function
See attached workbook where the Actual sum of all the totals for December is: 303,284.39
but the Sum totaled using code is: 303,281.00
Would someone be so kind as to point out why that is happening?
Thanks
Private Sub CommandButton1_Click()
'modified slightly Code found at this link
'http://www.dailydoseofexcel.com/archives/2008/11/26/autofiltering-on-months/
Dim lMonth As Long, lYear As Long, rngCell As Range, rngCol As Range
Dim LastRow As Long, wks As Worksheet
Dim DateCell As Range, rng As Range, x As Long, LValue As String
Set wks = ActiveSheet
With wks
LastRow = ActiveSheet.Range("D" & Rows.Count).End(xlUp).Row
If ActiveCell.Row = 1 Or ActiveCell.Row > LastRow Then
MsgBox "Please select a row that has data"
Exit Sub
End If
Set rngCol = ActiveSheet.Range("D2:D" & LastRow) ' Cost Total Column
Set rngCell = Range("A2:A" & LastRow) ' Date Column
Set DateCell = ActiveCell.EntireRow.Cells(1)
DateCell.Select
If Not rngCell Is Nothing Then
If IsDate(DateCell.Value) Then
lMonth = Month(DateCell.Value)
lYear = Year(DateCell.Value)
'Check if there is an autofilter
If rngCell.Parent.AutoFilterMode Then
'Make sure DateCell is within autofilter range
If Not Intersect(DateCell, _
rngCell.Parent.AutoFilter.Range) Is Nothing Then
'Create filter
With rngCell.Parent.AutoFilter
.Range.AutoFilter DateCell.Column - .Range(1).Column + 1, _
">=" & DateSerial(lYear, lMonth, 1), _
xlAnd, _
"<=" & DateSerial(lYear, lMonth + 1, 0)
End With
End If
End If
End If
End If
x = Sum_Visible_Cells(rngCol)
LValue = Format(x, "Currency")
MsgBox LValue
Set rng = .Range(.Cells(1, 1), .Cells(LastRow, 4))
If Not rng Is Nothing Then
'turn filter off
.AutoFilterMode = False
'reset filter
rng.AutoFilter
.EnableAutoFilter = True
End If
End With
End Sub
Function Sum_Visible_Cells(Cells_To_Sum As Object)
Dim aCell As Range
Dim total As Long
Application.Volatile
For Each aCell In Cells_To_Sum
If aCell.Rows.Hidden = False Then
If aCell.Columns.Hidden = False Then
total = total + aCell.Value
End If
End If
Next
Sum_Visible_Cells = total
End Function