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
list.

The function was looking as follows:


Public Function rcmnf(eqn1)


Dim v is Array(NF_range)


'comment: where NF_range is the name given to a range of cells
containing the values I wish to check the "aggregate boolean frequency"

of occurrence in the single cell the function points toward


For Each eqn1 In Selection


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


Next


End Function


When I say "aggregate boolean frequency" what I mean is take the first
value in NF_range and if it exists in the string on one or more
occasion(s), then count this as value 1 and move on to the next value
in NF_Range, and if that exists in the string on one or more
occasion(s), then count this as value 1 and add it to the previous
running total from the prior values checked in NF_Range, etc. etc.

I know this custom function isn't right, but I'm unsure about how to
proceed to
adapt your suggestion. Thank you for anyone providing thoughts already.


Any further thoughts? Thanks.

Mike

Public Function rcmnf(eqn1)


Dim v as Variant

v = Range("NF_range").Value


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

Put the function in a general/standard module (insert=Module in the VBE).
Not in the sheet module or the ThisWorkbook module.

--
Regards,
Tom Ogilvy

"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
list.

The function was looking as follows:


Public Function rcmnf(eqn1)


Dim v is Array(NF_range)


'comment: where NF_range is the name given to a range of cells
containing the values I wish to check the "aggregate boolean frequency"

of occurrence in the single cell the function points toward


For Each eqn1 In Selection


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


Next


End Function


When I say "aggregate boolean frequency" what I mean is take the first
value in NF_range and if it exists in the string on one or more
occasion(s), then count this as value 1 and move on to the next value
in NF_Range, and if that exists in the string on one or more
occasion(s), then count this as value 1 and add it to the previous
running total from the prior values checked in NF_Range, etc. etc.

I know this custom function isn't right, but I'm unsure about how to
proceed to
adapt your suggestion. Thank you for anyone providing thoughts already.


Any further thoughts? Thanks.

Mike

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
list.

The function was looking as follows:


Public Function rcmnf(eqn1)


Dim v is Array(NF_range)


'comment: where NF_range is the name given to a range of cells
containing the values I wish to check the "aggregate boolean frequency"

of occurrence in the single cell the function points toward


For Each eqn1 In Selection


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


Next


End Function


When I say "aggregate boolean frequency" what I mean is take the first
value in NF_range and if it exists in the string on one or more
occasion(s), then count this as value 1 and move on to the next value
in NF_Range, and if that exists in the string on one or more
occasion(s), then count this as value 1 and add it to the previous
running total from the prior values checked in NF_Range, etc. etc.

I know this custom function isn't right, but I'm unsure about how to
proceed to
adapt your suggestion. Thank you for anyone providing thoughts already.


Any further thoughts? Thanks.

Mike

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
list.

The function was looking as follows:


Public Function rcmnf(eqn1)


Dim v is Array(NF_range)


'comment: where NF_range is the name given to a range of cells
containing the values I wish to check the "aggregate boolean frequency"

of occurrence in the single cell the function points toward


For Each eqn1 In Selection


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


Next


End Function


When I say "aggregate boolean frequency" what I mean is take the first
value in NF_range and if it exists in the string on one or more
occasion(s), then count this as value 1 and move on to the next value
in NF_Range, and if that exists in the string on one or more
occasion(s), then count this as value 1 and add it to the previous
running total from the prior values checked in NF_Range, etc. etc.

I know this custom function isn't right, but I'm unsure about how to
proceed to
adapt your suggestion. Thank you for anyone providing thoughts already.


Any further thoughts? Thanks.

Mike

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
list.

The function was looking as follows:


Public Function rcmnf(eqn1)


Dim v is Array(NF_range)


'comment: where NF_range is the name given to a range of cells
containing the values I wish to check the "aggregate boolean frequency"

of occurrence in the single cell the function points toward


For Each eqn1 In Selection


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


Next


End Function


When I say "aggregate boolean frequency" what I mean is take the first
value in NF_range and if it exists in the string on one or more
occasion(s), then count this as value 1 and move on to the next value
in NF_Range, and if that exists in the string on one or more
occasion(s), then count this as value 1 and add it to the previous
running total from the prior values checked in NF_Range, etc. etc.

I know this custom function isn't right, but I'm unsure about how to
proceed to
adapt your suggestion. Thank you for anyone providing thoughts already.


Any further thoughts? Thanks.

Mike

What was wrong with the answer. It doesn't work.

--
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
list.

The function was looking as follows:


Public Function rcmnf(eqn1)


Dim v is Array(NF_range)


'comment: where NF_range is the name given to a range of cells
containing the values I wish to check the "aggregate boolean
frequency"

of occurrence in the single cell the function points toward


For Each eqn1 In Selection


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


Next


End Function


When I say "aggregate boolean frequency" what I mean is take the first
value in NF_range and if it exists in the string on one or more
occasion(s), then count this as value 1 and move on to the next value
in NF_Range, and if that exists in the string on one or more
occasion(s), then count this as value 1 and add it to the previous
running total from the prior values checked in NF_Range, etc. etc.

I know this custom function isn't right, but I'm unsure about how to
proceed to
adapt your suggestion. Thank you for anyone providing thoughts
already.


Any further thoughts? Thanks.

Mike

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
list.

The function was looking as follows:


Public Function rcmnf(eqn1)


Dim v is Array(NF_range)


'comment: where NF_range is the name given to a range of cells
containing the values I wish to check the "aggregate boolean
frequency"

of occurrence in the single cell the function points toward


For Each eqn1 In Selection


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


Next


End Function


When I say "aggregate boolean frequency" what I mean is take the first
value in NF_range and if it exists in the string on one or more
occasion(s), then count this as value 1 and move on to the next value
in NF_Range, and if that exists in the string on one or more
occasion(s), then count this as value 1 and add it to the previous
running total from the prior values checked in NF_Range, etc. etc.

I know this custom function isn't right, but I'm unsure about how to
proceed to
adapt your suggestion. Thank you for anyone providing thoughts
already.


Any further thoughts? Thanks.

Mike

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
list.

The function was looking as follows:


Public Function rcmnf(eqn1)


Dim v is Array(NF_range)


'comment: where NF_range is the name given to a range of cells
containing the values I wish to check the "aggregate boolean
frequency"

of occurrence in the single cell the function points toward


For Each eqn1 In Selection


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


Next


End Function


When I say "aggregate boolean frequency" what I mean is take the first
value in NF_range and if it exists in the string on one or more
occasion(s), then count this as value 1 and move on to the next value
in NF_Range, and if that exists in the string on one or more
occasion(s), then count this as value 1 and add it to the previous
running total from the prior values checked in NF_Range, etc. etc.

I know this custom function isn't right, but I'm unsure about how to
proceed to
adapt your suggestion. Thank you for anyone providing thoughts
already.


Any further thoughts? Thanks.

Mike

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
list.

The function was looking as follows:


Public Function rcmnf(eqn1)


Dim v is Array(NF_range)


'comment: where NF_range is the name given to a range of cells
containing the values I wish to check the "aggregate boolean
frequency"

of occurrence in the single cell the function points toward


For Each eqn1 In Selection


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


Next


End Function


When I say "aggregate boolean frequency" what I mean is take the
first
value in NF_range and if it exists in the string on one or more
occasion(s), then count this as value 1 and move on to the next
value
in NF_Range, and if that exists in the string on one or more
occasion(s), then count this as value 1 and add it to the previous
running total from the prior values checked in NF_Range, etc. etc.

I know this custom function isn't right, but I'm unsure about how
to
proceed to
adapt your suggestion. Thank you for anyone providing thoughts
already.


Any further thoughts? Thanks.

Mike

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
list.

The function was looking as follows:


Public Function rcmnf(eqn1)


Dim v is Array(NF_range)


'comment: where NF_range is the name given to a range of cells
containing the values I wish to check the "aggregate boolean
frequency"

of occurrence in the single cell the function points toward


For Each eqn1 In Selection


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


Next


End Function


When I say "aggregate boolean frequency" what I mean is take the
first
value in NF_range and if it exists in the string on one or more
occasion(s), then count this as value 1 and move on to the next
value
in NF_Range, and if that exists in the string on one or more
occasion(s), then count this as value 1 and add it to the previous
running total from the prior values checked in NF_Range, etc. etc.

I know this custom function isn't right, but I'm unsure about how
to
proceed to
adapt your suggestion. Thank you for anyone providing thoughts
already.


Any further thoughts? Thanks.

Mike

OK many thanks for the revisions and help - much appreciated

Mike


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
list.

The function was looking as follows:


Public Function rcmnf(eqn1)


Dim v is Array(NF_range)


'comment: where NF_range is the name given to a range of cells
containing the values I wish to check the "aggregate boolean
frequency"

of occurrence in the single cell the function points toward


For Each eqn1 In Selection


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


Next


End Function


When I say "aggregate boolean frequency" what I mean is take the
first
value in NF_range and if it exists in the string on one or more
occasion(s), then count this as value 1 and move on to the next
value
in NF_Range, and if that exists in the string on one or more
occasion(s), then count this as value 1 and add it to the previous
running total from the prior values checked in NF_Range, etc. etc.

I know this custom function isn't right, but I'm unsure about how
to
proceed to
adapt your suggestion. Thank you for anyone providing thoughts
already.


Any further thoughts? Thanks.

Mike

You are welcome !

And I apologize for the "bifurcation" we took in the discussion.
;-]

Since your function is using a range (NF_range) that is not passed as a
parameter, make sure to include this line:

Application.volatile

into it, or it will not recalculate automatically when you make changes to
NF_range.

--
Festina Lente


"MikeCM" wrote:

OK many thanks for the revisions and help - much appreciated

Mike


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
list.

The function was looking as follows:


Public Function rcmnf(eqn1)


Dim v is Array(NF_range)


'comment: where NF_range is the name given to a range of cells
containing the values I wish to check the "aggregate boolean
frequency"

of occurrence in the single cell the function points toward


For Each eqn1 In Selection


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


Next


End Function


When I say "aggregate boolean frequency" what I mean is take the
first
value in NF_range and if it exists in the string on one or more
occasion(s), then count this as value 1 and move on to the next
value
in NF_Range, and if that exists in the string on one or more
occasion(s), then count this as value 1 and add it to the previous
running total from the prior values checked in NF_Range, etc. etc.

I know this custom function isn't right, but I'm unsure about how
to
proceed to
adapt your suggestion. Thank you for anyone providing thoughts
already.


Any further thoughts? Thanks.

Mike

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
list.

The function was looking as follows:


Public Function rcmnf(eqn1)


Dim v is Array(NF_range)


'comment: where NF_range is the name given to a range of cells
containing the values I wish to check the "aggregate boolean
frequency"

of occurrence in the single cell the function points toward


For Each eqn1 In Selection


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


Next


End Function


When I say "aggregate boolean frequency" what I mean is take the
first
value in NF_range and if it exists in the string on one or more
occasion(s), then count this as value 1 and move on to the next
value
in NF_Range, and if that exists in the string on one or more
occasion(s), then count this as value 1 and add it to the
previous
running total from the prior values checked in NF_Range, etc.
etc.

I know this custom function isn't right, but I'm unsure about
how
to
proceed to
adapt your suggestion. Thank you for anyone providing thoughts
already.


Any further thoughts? Thanks.

Mike

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
list.

The function was looking as follows:


Public Function rcmnf(eqn1)


Dim v is Array(NF_range)


'comment: where NF_range is the name given to a range of cells
containing the values I wish to check the "aggregate boolean
frequency"

of occurrence in the single cell the function points toward


For Each eqn1 In Selection


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


Next


End Function


When I say "aggregate boolean frequency" what I mean is take the
first
value in NF_range and if it exists in the string on one or more
occasion(s), then count this as value 1 and move on to the next
value
in NF_Range, and if that exists in the string on one or more
occasion(s), then count this as value 1 and add it to the
previous
running total from the prior values checked in NF_Range, etc.
etc.

I know this custom function isn't right, but I'm unsure about
how
to
proceed to
adapt your suggestion. Thank you for anyone providing thoughts
already.


Any further thoughts? Thanks.

Mike

The "similar" values are the cause of your problems.

What are the characters allowed in "values" of the NF_range ?
(a to z, A to Z, _) ?

--
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
list.

The function was looking as follows:


Public Function rcmnf(eqn1)


Dim v is Array(NF_range)


'comment: where NF_range is the name given to a range of cells
containing the values I wish to check the "aggregate boolean
frequency"

of occurrence in the single cell the function points toward


For Each eqn1 In Selection


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


Next


End Function


When I say "aggregate boolean frequency" what I mean is take the
first
value in NF_range and if it exists in the string on one or more
occasion(s), then count this as value 1 and move on to the next
value
in NF_Range, and if that exists in the string on one or more
occasion(s), then count this as value 1 and add it to the
previous
running total from the prior values checked in NF_Range, etc.
etc.

I know this custom function isn't right, but I'm unsure about
how
to
proceed to
adapt your suggestion. Thank you for anyone providing thoughts
already.


Any further thoughts? Thanks.

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
list.

The function was looking as follows:


Public Function rcmnf(eqn1)


Dim v is Array(NF_range)


'comment: where NF_range is the name given to a range of cells
containing the values I wish to check the "aggregate boolean
frequency"

of occurrence in the single cell the function points toward


For Each eqn1 In Selection


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


Next


End Function


When I say "aggregate boolean frequency" what I mean is take the
first
value in NF_range and if it exists in the string on one or more
occasion(s), then count this as value 1 and move on to the next
value
in NF_Range, and if that exists in the string on one or more
occasion(s), then count this as value 1 and add it to the
previous
running total from the prior values checked in NF_Range, etc.
etc.

I know this custom function isn't right, but I'm unsure about
how
to
proceed to
adapt your suggestion. Thank you for anyone providing thoughts
already.


Any further thoughts? Thanks.

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
list.

The function was looking as follows:


Public Function rcmnf(eqn1)


Dim v is Array(NF_range)


'comment: where NF_range is the name given to a range of cells
containing the values I wish to check the "aggregate boolean
frequency"

of occurrence in the single cell the function points toward


For Each eqn1 In Selection


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


Next


End Function


When I say "aggregate boolean frequency" what I mean is take the
first
value in NF_range and if it exists in the string on one or more
occasion(s), then count this as value 1 and move on to the next
value
in NF_Range, and if that exists in the string on one or more
occasion(s), then count this as value 1 and add it to the
previous
running total from the prior values checked in NF_Range, etc.
etc.

I know this custom function isn't right, but I'm unsure about
how
to
proceed to
adapt your suggestion. Thank you for anyone providing thoughts
already.


Any further thoughts? Thanks.

My last function should not count those partial matches !

Can you give me an exact example where it is not working ?

--
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
list.

The function was looking as follows:


Public Function rcmnf(eqn1)


Dim v is Array(NF_range)

Itried again with your values, on the string
AA_X_EU_PER C_EXP_CT(0)
Iget a count of 1.
--
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
list.

The function was looking as follows:


Public Function rcmnf(eqn1)


Dim v is Array(NF_range)

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
list.

The function was looking as follows:


Public Function rcmnf(eqn1)


Dim v is Array(NF_range)

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
list.

The function was looking as follows:


Public Function rcmnf(eqn1)


Dim v is Array(NF_range)

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