Excel VBA: Counting Cells that do/don't contain certain text.

[ceb07]

I've been doing very basic Excel VBA for a few years but I'm dying with this problem:

I have a column of text (exported from a MSP schedule or other tools into Excel) and in the cells the text contains certain coding (a number followed by "FF","FS",or "SS"). Those codes are separated by commas in the same cell.

I want to know how many cells contain "SS" but do not contain a comma. The Find method in VBA seems restrictive to only one criteria...I can't figure out how to get two or more to work.

I know this is easy to do using filters in excel, but I'm trying desperately to automate it in a macro.

I would appreciate any tips or tricks that someone might be able to provide or a swift kick in the butt to go look at an existing thread; I'm new to the forum.

Thanks, CEB

 

[AnotherHiggins]

Rereading your question I'm not sure why you need to use a macro.

Type this into an empty cell:

[COLOR=blue white]=COUNTIF(A:A,"*FS") - COUNTIF(A:A,"*, FS")[/color]

(replace A with whatever column your data is in)

That works if you really just want to know, "how many cells contain "SS" but do not contain a comma". Is that what you need, or do you want to filter down to only those rows?

If you know how to do it with filters, just turn on your macro recorder (Tools > Macro > Record new macro) and go through the process of using filters, then observe the code that was generated.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.