Count number of appearance in a cell

  • Thread starter Thread starter daronnebonneau
  • Start date Start date
D

daronnebonneau

Hi,

I would like to count how many times a certain word appears in a cell.
The cell is supposed to show a word "Q", and disappear if it doesn't
meet my if formula under the cell.
since the cell is linked to DDE, I will show and disappears very
often.
And I would like to count how many times does the" Q" appears during a
certain time period.
Does anyone have idea how to write logic ?

Sub test()
Dim Count As Interger
 
does the dde force a calculate? if so, you can trap that event and update a
counter

right click the sheet's tab and select View Code
in the module paste this code, then change QCELL and COUNTER for the actual
cell addresses:


Option Explicit
Private Sub Worksheet_Calculate()
If Range("QCELL") = "Q" Then Range("COUNTER").Value = Range("COUNTER").Value
+ 1
End Sub
 
=SUM(LEN(A1:A3)-LEN(SUBSTITUTE(A1:A3,"Q","")))
Enter with Ctrl+Shift+Enter, not just Enter.
HTH,
Ryan---
 
Back
Top