| Home | Forums | Reviews | Articles | Register |
 |
| Thread Tools | Rate Thread |
|
Guest
Posts: n/a
|
|
|
|
||
|
|
| |
|
Guest
Posts: n/a
|
|
|
|
|
||
|
Guest
Posts: n/a
|
|
|
|
|
||
|
Guest
Posts: n/a
|
|
|
|
|
||
|
Guest
Posts: n/a
|
|
|
|
|
||
|
Guest
Posts: n/a
|
What was wrong with the answer. It doesn't work.
-- Regards, Tom Ogilvy "PapaDos" <(E-Mail Removed)> wrote in message news  6A22ED2-64D8-4AF3-8DEA-(E-Mail Removed)...> 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" <(E-Mail Removed)> wrote in message >> news:578ED6E0-E5DD-48AA-92D6-(E-Mail Removed)... >> > 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 >> >> >> >> >> >> >> |
|
|
|
|
|||
|
Guest
Posts: n/a
|
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" <(E-Mail Removed)> wrote in message news 6A22ED2-64D8-4AF3-8DEA-(E-Mail Removed)...> 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" <(E-Mail Removed)> wrote in message >> news:578ED6E0-E5DD-48AA-92D6-(E-Mail Removed)... >> > 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 >> >> >> >> >> >> >> |
|
|
|
|
|||
|
Guest
Posts: n/a
|
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" <(E-Mail Removed)> wrote in message > news 6A22ED2-64D8-4AF3-8DEA-(E-Mail Removed)...> > 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" <(E-Mail Removed)> wrote in message > >> news:578ED6E0-E5DD-48AA-92D6-(E-Mail Removed)... > >> > 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 > >> >> > >> >> > >> > >> > >> > > > |
|
|
|
|
|||
|
Guest
Posts: n/a
|
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" <(E-Mail Removed)> wrote in message news:6C2A3DAD-4B39-48D5-868E-(E-Mail Removed)... > 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" <(E-Mail Removed)> wrote in message >> news 6A22ED2-64D8-4AF3-8DEA-(E-Mail Removed)...>> > 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" <(E-Mail Removed)> wrote in message >> >> news:578ED6E0-E5DD-48AA-92D6-(E-Mail Removed)... >> >> > 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 >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> |
|
|
|
|
|||
|
Guest
Posts: n/a
|
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" <(E-Mail Removed)> wrote in message > news:6C2A3DAD-4B39-48D5-868E-(E-Mail Removed)... > > 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" <(E-Mail Removed)> wrote in message > >> news 6A22ED2-64D8-4AF3-8DEA-(E-Mail Removed)...> >> > 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" <(E-Mail Removed)> wrote in message > >> >> news:578ED6E0-E5DD-48AA-92D6-(E-Mail Removed)... > >> >> > 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 > >> >> >> > >> >> >> > >> >> > >> >> > >> >> > >> > >> > >> > > > |
|
|
|
|
|||
|
|
| |
|
| Thread Tools | |
| Rate This Thread | |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| Search for values in between two values in a string? | pat | Microsoft VB .NET | 14 | 13th May 2009 08:33 PM |
| Search for values in between two values in a string? | pat | Microsoft VB .NET | 0 | 13th May 2009 06:13 AM |
| (another) Custom function to search text string for values occuring within it | MikeCM | Microsoft Excel Programming | 0 | 11th Oct 2006 09:01 AM |
| Custom Search String | Max Smart | Microsoft Access Form Coding | 2 | 19th Dec 2004 11:59 PM |
| Option Group values converted to Text String values in reports | Fons Ponsioen | Microsoft Access Reports | 3 | 18th May 2004 10:50 PM |
