Count number of appearance in a cell

[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

 

[Patrick Molloy]

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

 

[ryguy7272]

=SUM(LEN(A1:A3)-LEN(SUBSTITUTE(A1:A3,"Q","")))
Enter with Ctrl+Shift+Enter, not just Enter.
HTH,
Ryan---