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