sum by format

[lior03]

hello
i am trying to use a the following style
style is #,###,",000"
to round figures to the nearest thousenth
when i sum the figures i get a result which defy aritmetics
[VBA]like 22000+23000=46000 funny...[/VBA]
i thought that a sum function adjused for number format will do the trick
that how far i got
[VBA]
Function Sumwhole(rng As Range, Optional fmt As String = "0.00%")
Dim Cell As Range, amt As Double
For Each Cell In rng
If IsMissing(fmt) Then fmt = #, ### ,",000 "
If Cell.NUMBERFORMAT = fmt Then
If IsNumeric(Cell.Value) Then
Sumwhole = Sumwhole + Cell.Value
End If
End If
Next Cell
End Function

[/VBA]

[Charlize]

[vba]Function Sumwhole(rng As Range, Optional fmt As String = "0.00%")
Dim Cell As Range, amt As Double
For Each Cell In rng
If Cell.NumberFormat <> fmt And IsNumeric(Cell.Value) Then
Cell.NumberFormat = "#'##0.00"
If IsNumeric(Cell.Value) Then
Sumwhole = Sumwhole + Cell.Value
End If
End If
Next Cell
End Function[/vba]

This should count the items in a range that are numeric with or without the format. After the counting the cells that are counted have the style #'##0.00

i've tried it with [vba]activecell.value = Round(Sumwhole(range("A1:A30")))[/vba]
This means that numeric values in this range will be counted and decimals will be rounded to 1 or 0 (the cell with the result in it). Example : a1 = 100000.30 a2 : 100000.10

When you go to C1 and run the macro you'll get 200000 instead off 200000.40

Charlize

[Bob Phillips]

This is because the numbers are invariably not 23,000 and 22,000 and so the sum is greater than 45,499.

Try adding like so

=SUM(ROUND(E1:E2,-3))

[lior03]

hello xld
could i use a udf like:
[VBA]Function sumwhole(r As Range)
sumwhole = Application.WorksheetFunction.Sum(Round(r, -3))
End Function
[/VBA]
thanks

[Bob Phillips]

We've been here before. Why on earth would you use a UDF that solely calls back into a worksheet function? Two round trips to get back where you started.

[lior03]

dear xld
because i get a value error when u use your formula.
thanks

[Bob Phillips]

wrapping it in a UDF won't get rid of the error.

[lior03]

dear xld
my solution:
[VBA]
Sub xldion()
selection.FormulaArray = "=SUM(ROUND(R[-2]C:R[-1]C,-3))"
End Sub

[/VBA]
thanks for all

[lior03]

hello xld
my last question on this matter.i promise.
how can i turn the formula in the previous example to act like the sigma icon.namely a click on the icon will automaticlly calculate the sum.

[VBA]Sub xldion()
selection.FormulaArray = "=SUM(ROUND(R[-2]C:R[-1]C,-3))"
End Sub[/VBA]
thanks

[Bob Phillips]

Create a button and assign this code to the button

[vba]

Sub xldion()
Dim FirstRow As Long
With Selection
FirstRow = .Offset(0, -1).End(xlUp).Row
.FormulaArray = "=SUM(ROUND(R" & FirstRow & "C:R[-1]C,-3))"
End With
End Sub
[/vba]