Searching each cell for any combination of 3 characters

**ravl13** · 09-07-2010, 10:57 AM

Hi there,

I'm currently working on a macro that tallies each "A", "G", and "S" that occurs in a column. So, if a cell's contents was "AG", I would like variables atally and gtally to each get +1 to their value. I would also like atally and gtally to each get +1 if the contents are "GA" as well.

I initially only had to work with "G" and "S", and used a "B" to indicate *both G & S*, but the introduction of a new value "A" screws things up big time.

Currently, my relevant portion of the macro is essentially:
(This macro starts by selecting the first cell value of the column and moves down to the next cell right before it loops)

Please Login or Register  to view this content.

What I would like it to look like is this:

Please Login or Register  to view this content.

How do I do this?

**Andrew-R** · 09-07-2010, 11:00 AM

If you just want to see if a cell contains a certain character then you can use:

Please Login or Register  to view this content.

I don't think you want to use ElseIf, as that will only be evaluated if the original If condition fails, i.e. in your example it would only check for S if there was no G in the cell and only check for A if there was no S.

**ravl13** · 09-07-2010, 11:46 AM

Thanks! Just tried it out and that's exactly what I need

**Ron Coderre** · 09-07-2010, 11:59 AM

Perhaps this code will give you something to work with:

Please Login or Register  to view this content.

**ravl13** · 09-07-2010, 01:35 PM

Originally Posted by Ron Coderre

Perhaps this code will give you something to work with:

Please Login or Register  to view this content.

Any lines above with "*******" next to them, I don't quite understand what they are doing. Would you (or anyone else looking) mind explaining what is going on in simpler terms?

**Ron Coderre** · 09-07-2010, 02:11 PM

Please Login or Register  to view this content.

• Resizes the selection to only the top left cell

Please Login or Register  to view this content.

• Expands that selection down to the last non-blank cell

Please Login or Register  to view this content.

• Increments G_Count by the value returned from:

Please Login or Register  to view this content.

• The InStr function returns the position of the found text...Zero if the text is not found.
• We want to know if the position is greater than 0.
• In VBA, TRUE = -1...so we need to subtract the returned value to INCREASE the count. (FALSE = 0)

As the code progresses down the column of cells, each "hit" of a letter increases the count for that letter.

Does that help?

**Andrew-R** · 09-07-2010, 02:32 PM

My gut feeling is that this is fundamentally the wrong way to go about the problem. From the original post we know that the letters can change, so a better solution would be to have the letters and their counts inside and array or collection. That would allow the code to be generalised and be far easier to update.

Also, and this is probably just me, but I don't like using the literal value of constants in mathematical expressions. To me that always seems to be asking for version incompatibility problems.