stupid count question

  • Thread starter Thread starter Rich31730
  • Start date Start date
R

Rich31730

Hi
I have searched the forums and Google w/ no luck. Please help this is such a
basic question that I cannot find the answer.

1.) I only have 1 cell (say A1) with multiple values (e.g.
=123+125+125+....) which gives me a total of 373.

2.) In cell A2 only want to count the numbers with in cell A1 (e.g. 3) would
be the answer.

3.) I do not want to use the =(Count(value1,value2,....). Because then I
would have to enter the numbers in twice. I realize it is like the text
count idea, but cannot figure out how to cross implement the idea.

Thanks
Rich
 
The number of items in the example formula is the number of "+" signs plus
one. First enter the following UDF:

Function formla(r As Range) As String
formla = r.Formula
End Function

If your formula is in A1, then in another cell:

=LEN(formla(A1))-LEN(SUBSTITUTE(formla(A1),"+",""))+1
 
Hi and thank you,
I entered the function into vba under the "general declartations" heading
for the given sheet.
1.) when entered in vba changes the r.Formula to r.formula then when I place
the given formula =LEN(formla(A1))-LEN(SUBSTITUTE(formla(A1),"+",""))+1
into cell A2.

I get a "#NAME?" error. What am I doing wrong
 
The error indicates that the UDF is in the wrong place.

Erase it first, so we will not have two copies.


UDFs are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it.

To use the UDF from the normal Excel window, just enter it like a normal
Excel Function

To remove the UDF:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To learn more about UDFs, see:

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx
 
Hi again and thanks for the reply.
Had to start all over and it worked fine and thank you very much.
Rich
 
Back
Top