-
sum by format
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]
moshe
-
[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
Last edited by Charlize; 09-27-2006 at 12:54 PM.
-
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))
-
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
moshe
-
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.
-
dear xld
because i get a value error when u use your formula.
thanks
moshe
-
wrapping it in a UDF won't get rid of the error.
-
dear xld
my solution:
[VBA]
Sub xldion()
selection.FormulaArray = "=SUM(ROUND(R[-2]C:R[-1]C,-3))"
End Sub
[/VBA]
thanks for all
moshe
-
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
moshe
-
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]
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules