Sleeper: Summing based on numberformat

[lior03]

hello
i have a table in which rows contains cell with different numberformats - #,##0.00 or 0.00% i built this vba function called server.
i want the function to sum only cells with numberformat-#,##0.00
whats wrong?

Function server(r As Range) 
Dim cell As Range
Dim i As Integer
For Each cell In Selection
If cell.NumberFormat = "#,##0.00" Then
i = cell.Value + 1
Else: Exit Function
i = server
End If
Next
End Function

thanks

[Norie]

moshe

What do you mean 'what's wrong'?

Is the function returning an incorrect answer or not working at all.

By the way it looks to me as though you are counting the no of times that number format is being used rather than summing.

[Cyberdude]

I really can't answer your Q, but I do suggest that you remove the colon following the Else .

[Norie]

Cyberdude

That colon is acceptable syntax. Though I do agree it should be removed, if only to clarify the flow of the code.

[Bob Phillips]

hello
i have a table in which rows contains cell with different numberformats - #,##0.00 or 0.00% i built this vba function called server.
i want the function to sum only cells with numberformat-#,##0.00
whats wrong?
Function server(r As Range)
Dim cell As Range
Dim i As Integer
For Each cell In Selection
If cell.NumberFormat = "#,##0.00" Then
i = cell.Value + 1
Else: Exit Function
i = server
End If
Next
End Function
thanks

A couple of things.

First you are counting not summing, and secondly you have an argument of r as a Range, but then ignore it and process the selected cells.

How do you call it, and what is wrong for you?

[johnske]

Hi Moshe,

Try this

Option Explicit

Sub Test()
      'first format some cells in #,##0.00 format
      'then make a selection & run this sub
      server Selection
End Sub

Function server(r As Range)
      'I've added message boxes so you
      'can see what's happening here
      Dim cell As Range
      Dim i As Integer
      For Each cell In Selection
            If cell.NumberFormat = "#,##0.00" Then
                  i = cell.Value + 1
                  MsgBox i
            Else
                  i = server  '< maybe you mean this?
                  MsgBox i
                  Exit Function
                  'i = server  '< does nothing here
            End If
      Next
End Function

HTH,
John

[mdmackillop]

I'm not sure if this is quite what you're after, but the following as a UDF (user defined formula) should return the total as you have specified. It's entered as =server(A1:A10)

Function server(r As Range)
    Dim cell As Range
    Dim i As Double
    For Each cell In r
        If cell.NumberFormat = "#,##0.00" Then
            i = i + cell
        End If
    Next
    server = i
End Function

[MWE]

I'm not sure if this is quite what you're after, but the following as a UDF (user defined formula) should return the total as you have specified. It's entered as =server(A1:A10)

Function server(r As Range)
    Dim cell As Range
    Dim i As Double
    For Each cell In r
        If cell.NumberFormat = "#,##0.00" Then
            i = i + cell
        End If
    Next
    server = i
End Function

I believe that the main problems all along were:
1. the incorrect summing statement (originally i = cell.value + 1)
2. no assignment of a summed value to the function name
both of which you corrected in your revised UDF.

[lior03]

hello
is it possible to add to the following function
a an optional argument which will enable the user to select the numberformat he desire?

Function server(r As Range,optional fmt as string) 
Dim cell As Range
Dim i As Double
For Each cell In r
If cell.NumberFormat = fmt Then
i = i + cell
End If
Next
server = i
server = Format(server, "##,###.00")
End Function

thanks

[mdmackillop]

Hi,
You can do that

Function server(r As Range, Optional fmt As String)
    Dim cell As Range
    Dim i As Double
    For Each cell In r
        If cell.NumberFormat = fmt Then
            i = i + cell
        End If
    Next
    server = Format(i, fmt)
End Function

As an alternative, you could exclude only items of a certain format

Function xpcsum(r As Range, Optional fmt As String)
    Dim cell As Range
    Dim i As Double
    For Each cell In r
        If Not cell.NumberFormat = fmt Then
            i = i + cell
        End If
    Next
    xpcsum = i
End Function

[Bob Phillips]

hello
is it possible to add to the following function
a an optional argument which will enable the user to select the numberformat he desire?

You seem to have done that already for the range being checked. so I assume that you mean the output format.

Also, your summing statement

i = i + cell

is incorrect, it should be

i = i + 1

How about this?

Function server(r As Range, Optional inFormat As String = "#,##0.00", _
                Optional outFormat As String = "#,##0")
    Dim cell As Range
    Dim i As Double
    For Each cell In r
        If cell.NumberFormat = inFormat Then
            i = i + 1
        End If
    Next
    server = Format(i, outFormat)
End Function

[lior03]

Function server1(r As Range, Optional fmt As String) 
Dim cell As Range, i As Double
If IsMissing(fmt) Then
fmt = "#,##0.00"
For Each cell In r
If cell.NumberFormat = fmt Then
i = i + cell
server1 = Format(i, fmt)
End If
Next
End If
End Function

what's wrong with
thanks

[mdmackillop]

Try
[VBA] If fmt = "" Then
fmt = "#,##0.00"[/VBA]
This code will only work where an actual font is set, not on General font which may "look like" the fmt setting.

[Bob Phillips]

Function server1(r As Range, Optional fmt As String)
Dim cell As Range, i As Double
If IsMissing(fmt) Then
fmt = "#,##0.00"
For Each cell In r
If cell.NumberFormat = fmt Then
i = i + cell
server1 = Format(i, fmt)
End If
Next
End If
End Function
what's wrong with
thanks

Moshe,

Where are you going with this? There have been numerous attempts to help you, but we are getting no feedback as to whether we are succeeding, getting close, or are miles off, and then you come up with this effort which is similar, but oddly different.

Help us to help you, please

[mdmackillop]

Hi Moshe,
I agree with XLD, we're not getting anywhere fast. Can you post a sample of your spreadsheet with the cells you're trying to "sum", so we can see if there is an underlying problem. To post a workbook, zip it and use Manage Attachments which you can access if you use the Go Advanced button
Regards
MD

[lior03]

hello
i did not mean to upset anyone.
attached is a workbook with the problem.
a database of economic data year by year .each column of data is ccompanied by a column of growth ratio to the year befor formated in "0.00%".
i am trying to build a UDF to deal with average growth rate ,as well as summig the data formated in "##,###.00"
for instance - in sheet gdpf how can i get the average of data formated in "0.00%".
thanks

[Norie]

moshe

There is no attachment.

[mdmackillop]

Hi Moshe,
I don't believe you have upset anyone, so no apologies needed;
but your workbook is not attached.

[lior03]

hello
i did not mean to upset anyone.
attached is a workbook with the problem.
a database of economic data year by year .each column of data is ccompanied by a column of growth ratio to the year befor formated in "0.00%".
i am trying to build a UDF to deal with average growth rate ,as well as summig the data formated in "##,###.00"
for instance - in sheet gdpf how can i get the average of data formated in "0.00%".
thanks

[mdmackillop]

Question continued in new post at http://www.vbaexpress.com/forum/showthread.php?t=3793