Tally occurences of phrase on one sheet.

[Guest]

I have several sheets of information. What I'm looking to do is tally the
occurences of a specific phrase "ABC" in one column, and place that tally on
a separate sheet. Anyone have any ideas.

 

[Guest]

The following code assumes that the data starts in column A, row 1 on a
worksheet that is not Sheet3 and that the workbook has at least 3 worksheets.

The macor is run while you are in the sheet containing the data you wish to
count

Sub CountABC()

Dim varVal As Variant
Dim lABCCount As Long
Dim lOffset As Long

Range("A1").Select
varVal = ActiveCell.Value

'loop down column A and count each occurrence of the
'string ABC storing to a variable lABCCount
'Increment the row offset variable lOffset by 1 and pick
'up the value in the next cell in column A

Do Until varVal = ""
If varVal = "ABC" Then
lABCCount = lABCCount + 1
End If
lOffset = lOffset + 1
varVal = ActiveCell.Offset(lOffset).Value
Loop

'Activate Sheet3 and select cell A1
'Post the count value
Sheets("Sheet3").Activate
Range("A1").Select
ActiveCell.Value = lABCCount

End Sub