tiehaze
asked on
Use vba to check total number of occurences
Here is a simplified version of what I am doing. Column A has different footnotes listed in each row:
S
S, L
@@, **
@
L
L
S
I need to count the total number or cells that has an L in it. Any ideas?
S
S, L
@@, **
@
L
L
S
I need to count the total number or cells that has an L in it. Any ideas?
leonstryker
Try the COUNTIF function
ASKER
How would I set up the criteria?
In column B next to each foot note put : =SEARCH("L",A1,1)
Then count column B values =COUNT(B:B)
If "L" is not in footnote the Search will display #VALUE and will not be counted by COUNT
Leon
Then count column B values =COUNT(B:B)
If "L" is not in footnote the Search will display #VALUE and will not be counted by COUNT
Leon
ASKER
Is there any other way of doing it within vba?
ASKER
If not, that will work.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
In VBA code you will need to still use the same Array formula or you can loop through the range and use the Instr function
Leon
Leon
ASKER
One minute, let me try some things out. I think I will be fine with the array.
ASKER
The array did not work. Can you try it out and see if you can figure it out?
{=COUNT(SEARCH("L",A1:A8,1
What are the outside brackets {} for? Do I need to keep those in there?
{=COUNT(SEARCH("L",A1:A8,1
What are the outside brackets {} for? Do I need to keep those in there?
Type in the formula, then hit Cntrl/Shift/Enter keys at the same time.
This is a special type of Excel formula. You do not type in the brakets. Excel puts them there for you.
Leon
This is a special type of Excel formula. You do not type in the brakets. Excel puts them there for you.
Leon