Printable View
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?
thanksCode: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
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.
I really can't answer your Q, but I do suggest that you remove the colon following the Else .
Cyberdude
That colon is acceptable syntax. Though I do agree it should be removed, if only to clarify the flow of the code.
A couple of things.Quote:
Originally Posted by lior03
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?
Hi Moshe,
Try this
HTH,Code: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
John
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)
Code: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:Quote:
Originally Posted by mdmackillop
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.
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?
thanksCode: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
Hi,
You can do that
As an alternative, you could exclude only items of a certain formatCode: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
Code: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
You seem to have done that already for the range being checked. so I assume that you mean the output format.Quote:
Originally Posted by lior03
Also, your summing statement
i = i + cell
is incorrect, it should be
i = i + 1
How about this?
Code: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
what's wrong withCode: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
thanks
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.
Moshe,Quote:
Originally Posted by lior03
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 :)
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
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
moshe
There is no attachment.
Hi Moshe,
I don't believe you have upset anyone, so no apologies needed;
but your workbook is not attached.
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
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.Quote:
Originally Posted by mdmackillop