PDA

View Full Version : Sleeper: Summing based on numberformat



lior03
06-25-2005, 12:52 PM
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
06-25-2005, 01:06 PM
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
06-25-2005, 01:08 PM
I really can't answer your Q, but I do suggest that you remove the colon following the Else .

Norie
06-25-2005, 01:13 PM
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
06-25-2005, 01:20 PM
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
06-25-2005, 03:11 PM
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
06-25-2005, 03:11 PM
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
06-25-2005, 05:54 PM
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
06-26-2005, 11:46 AM
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
06-26-2005, 12:12 PM
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
06-26-2005, 12:21 PM
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
06-26-2005, 12:54 PM
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
06-26-2005, 02:12 PM
Try
If fmt = "" Then
fmt = "#,##0.00"
This code will only work where an actual font is set, not on General font which may "look like" the fmt setting.

Bob Phillips
06-26-2005, 02:43 PM
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
06-26-2005, 03:39 PM
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
06-27-2005, 05:49 AM
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
06-27-2005, 05:53 AM
moshe

There is no attachment.

mdmackillop
06-27-2005, 05:55 AM
Hi Moshe,
I don't believe you have upset anyone, so no apologies needed;
but your workbook is not attached.

lior03
06-28-2005, 12:10 AM
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
06-28-2005, 05:53 AM
Question continued in new post at http://www.vbaexpress.com/forum/showthread.php?t=3793

Bob Phillips
06-28-2005, 06:17 AM
Question continued in new post at http://www.vbaexpress.com/forum/showthread.php?t=3793

Can you mark this as solved M D to park it.