Count number of appearance in a cell

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
 
P

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
 
R

ryguy7272

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top