Searching each cell for any combination of 3 characters

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)

Do until Selection.Value= ""

If selection.value= "G" then
      gtally = gtally + 1
elseif selection.value="S" then
      stally = stally + 1
elseif selection.value="B" then
    gtally = gtally + 1
    stally = stally + 1
end if

Selection.Offset(1, 0).Select

Loop

What I would like it to look like is this:

Do until Selection.Value= ""
   
If the cell contains the character "G" then
      gtally = gtally + 1
elseif the cell contains the character "S" then
      stally = stally + 1
elseif the cell contains the character "A" then
      atally = atally + 1
end if

Selection.Offset(1, 0).Select

Loop

How do I do this?

Perhaps this code will give you something to work with:

Sub CountGAS_Entries()
Dim rColCells As Range
Dim cCell As Range
Dim G_Count As Integer
Dim A_Count As Integer
Dim S_Count As Integer

Set rColCells = Selection.Cells(1.1)
Set rColCells = Range(rColCells, rColCells.End(Direction:=xlDown))

For Each cCell In rColCells
    If Len(cCell) = 0 Then
        GoTo ShowTally
    Else
        G_Count = G_Count - (InStr(1, UCase(cCell.Value), "G") > 0)
        A_Count = A_Count - (InStr(1, UCase(cCell.Value), "A") > 0)
        S_Count = S_Count - (InStr(1, UCase(cCell.Value), "S") > 0)
    End If
Next cCell
ShowTally:
MsgBox "G Count: " & G_Count & vbCr _
        & "A Count: " & A_Count & vbCr _
        & "S Count: " & S_Count
End Sub

Originally Posted by Ron Coderre

Perhaps this code will give you something to work with:

Sub CountGAS_Entries()
Set rColCells = Selection.Cells(1.1)  *****
Set rColCells = Range(rColCells, rColCells.End(Direction:=xlDown))  ******

For Each cCell In rColCells
    If Len(cCell) = 0 Then 
        GoTo ShowTally
    Else
        G_Count = G_Count - (InStr(1, UCase(cCell.Value), "G") > 0) *******
        A_Count = A_Count - (InStr(1, UCase(cCell.Value), "A") > 0)
        S_Count = S_Count - (InStr(1, UCase(cCell.Value), "S") > 0)
    End If
Next cCell

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?

Set rColCells = Selection.Cells(1.1)

• Resizes the selection to only the top left cell

Set rColCells = Range(rColCells, rColCells.End(Direction:=xlDown))

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

G_Count = G_Count - (InStr(1, UCase(cCell.Value), "G") > 0)

• Increments G_Count by the value returned from:

(InStr(1, UCase(cCell.Value), "G") > 0)

• 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?