About Us

PapaDos

What was wrong ?
--
Festina Lente

"MikeCM" wrote:

I think I have got it to work. Many thanks.

PapaDos wrote:
Do you have empty cells in NF_range ?
--
Festina Lente

"MikeCM" wrote:

You second suggestion also has the same problem.

I have checked a couple of specific examples of where this does not
work, and it is all about those "similar" values.

For example, both AA_X_EU_PERC and AA_X_EU_PER C_EXP_CT are separate
values within NF_range. When the count is run on a string that contains
AA_X_EU_PER C_EXP_CT (only once), the count returned is 2, as it is
finding both the full value and AA_X_EU_PERC.

Can the logic include reference to say count only once and nothing that
has an extension of _ after it. The extension would always be of the
form _.

Thanks for any help

Mike

PapaDos wrote:
Try this one:

Function rcmnf(eqn As String)
Static re

Application.Volatile
If IsEmpty(re) Then
Set re = CreateObject("VBScript.RegExp")
re.Global = True
re.MultiLine = True
re.Pattern = "$|^|\W"
End If
rcmnf = Application.Count(Application.Find([" " & NF_range & " "],
re.Replace(eqn, " ")))
End Function

--
Festina Lente

"MikeCM" wrote:

This still doesn't quite work for me. It's returning values, often
correctly but not always correctly. The function I have at present is
as follows:

Public Function rcmnf(eqn1)

Dim v As Variant

v = Range("NF_range").Value

Application.Volatile

For i = LBound(v, 1) To UBound(v, 1)
For j = LBound(v, 2) To UBound(v, 2)
cnt = Application.CountIf(eqn1, "*" & v(i, j) & "*")
totcnt = totcnt + cnt
Next j
Next i

rcmnf = totcnt

End Function

Something tells me that it's to do with the way in which the long
"string of text" is described; that is, the text which is being
searched for any instances of values described in the NF_range. This
"string of text" is actually the written form of an equation, and
potentially contains a number of the named values contained in the
NF_range. As a long form representation of the equation, the values
might variously be separated by +, -, /, *, ), (, or a space, but not
necessarily consistently. Might this have a bearing?

The other thing I can think of is that within NF_range, there are a few
instances of "similar" values. For example, values of AA_X_DOC_COMPLX
and also AA_X_DOC_COMPLX_PRJ. Might this be causing problems with the
referencing and counting?

Thanks very much for any further advice and assistance.

Mike

Tom Ogilvy wrote:
I don't know of any good ones that address what you speak of.

To the best of my knowledge, all the ones available through
worksheetfunction are available through application. The WorksheetFunction
object wasn't introduced until xl97. Before that Application was the only
qualifier. Using worksheetfunction as a qualifier caused Excel an type
error (#N/A for example) to raise trappable errors (1000 errors).
Application doesn't.

I think a lot of the confusion is the behavior of the functions themselves.
You see a lot of the "cool" stuff in worksheetfunctions revolves around
discovering the special behavior of some functions. Like
sum(countif(A:A,{"a","b","c"})) works normally entered, but some other
functions having an array would need to be array entered to function.
Again, I don't know any single site that focuses on such. Laraunt Longre
was one of the best at these type discoveries and has a site, but it
predominantly in French - if you read that or use a web translator that
might be a good place to go.

http://xcell05.free.fr/

--
Regards,
Tom Ogilvy

"PapaDos" wrote in message
...
No problem !
It is very easy to miss stuff with those antiquated "plain ascii"
forums...

A little challenging is a good motivator. ;-]

I got confused too, because the function seemed to fail at times.
I finally found out that I forgot to add an Application.Volatile line to
it.
It is needed because the range it uses is not passed as a parameter...

Do you have any good web references about how worksheet functions treat
arrays when called from VBA ?
I use them a lot because they are WAY faster than VBA iteration of ranges.
I often have to try my solutions inside-out to see if they work as I
expected. The way Excel processes arrays is tough to follow and
understand,
but it is much worst in VBA where we can't trace the way they are
evaluated.

Do you know exactly why some worksheet functions are available from the
Application object and others from the WorksheetFunction object ?

Those from the Application object seem to work better for me with arrays,
I
have no clue why !

I never really understood well those worksheet functions issues, even if I
use them a lot !

Thanks,
Luc.

--
Festina Lente

"Tom Ogilvy" wrote:

My apologies, I missed the [ ] around the defined name - so you are using
evaluate (only where it is needed - better than I suggest) and I missed
it.

--
Regards,
Tom Ogilvy

"PapaDos" wrote in message
...
Here is the array formula I used for testing my function:
{=COUNT( FIND( NF_range, eqn1 ) )}

I understand what you are saying, but the darned thing looked like it
works
fine with the examples I try it with.

I am puzzled...

Here is my NF_range:

this
value
long
with
alpha
beta
we'll
line

and here is the string (contains Alt-Enter) I pass as an argument to
the
function:

"this is a rather long entry
with more than one line
that we will scrutinize for values."

When I play with the string or the table, my darned function returns
the
same thing than the array formula.

Is my formula screwed too !

Any ideas ?
Can you give me an example where it fails ?

Regards,
Luc.

--
Festina Lente

"Tom Ogilvy" wrote:

Let me be clearer. The array function approach (which you didn't
show)
certainly works.

Your VBA function does not work as you have written it. It is not
equivalent to doing the same think with worksheet functions in a cell
with
array entry.

--
Regards,
Tom Ogilvy

"PapaDos" wrote in message
...
What is wrong with the answer ?

Thanks for the note about the parameter's name, I was a bit too
quick...
--
Festina Lente

"Tom Ogilvy" wrote:

Think you need to test that. VBA doesn't support array
interpretations
of
normal functions in most cases , this being one.

also str is a function in VBA, so poor choice as a variable.

You would need to use (Demo'd from the immediate window)

sStr = "the horse has a big head"
? Evaluate("Count(Find(Name1,""" & sstr & """))")
2

name1 was 3 cells containing the letters f a b
--
Regards,
Tom Ogilvy

"PapaDos" wrote in message
...
There is actually no need for a function, it can be done with an
array
formula, but here it is:

Function rcmnf(str)
rcmnf = Application.Count(Application.Find([NF_range], str))
End Function
--
Festina Lente

"MikeCM" wrote:

I tried to put it into a custom function, called "rcmf". This
function would have a single argument, eqn1, being a single cell
that
contains a big whole string of text made up of a series of
values,
which are separated variously within the cell by spaces,
parentheses,
etc..

I have a list of values elsewhere, a range of cells called
NF_Range.
The function is intended to return a total of all the TRUE
answers
for
each test of the string against each of the values listed in
NF_range

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Nested formula to search a text string and return specific text	Barbie	Excel Worksheet Functions	10	February 21st 09 07:40 AM
Search for a text string	Dan	Excel Discussion (Misc queries)	1	November 10th 08 09:09 PM
search a string as substring and give adjacent values	Eddy Stan	Excel Worksheet Functions	1	June 23rd 08 03:05 PM
Search, find or lookup defined text in text string	zzxxcc	Excel Worksheet Functions	9	September 6th 07 09:37 PM
VBA function : How to search a string in another string?	bibi-phoque	Excel Programming	5	April 19th 05 06:24 PM

Menu

About Us