Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count number of appearance in a cell
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count number of appearance in a cell
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 wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count number of appearance in a cell
=SUM(LEN(A1:A3)-LEN(SUBSTITUTE(A1:A3,"Q","")))
Enter with Ctrl+Shift+Enter, not just Enter. HTH, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Patrick Molloy" wrote: 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 wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count x2 Appearance of Numeric Value in any Row | Excel Worksheet Functions | |||
Count the number of appearance within a cell | Excel Worksheet Functions | |||
Count & Sum Consecutive (2x) appearance of Specific Numeric Values | Excel Programming | |||
Count & Sum Consecutive (2x) appearance of Specific Numeric Values | Excel Worksheet Functions | |||
number formats that allow you to format the appearance of negativ. | Charts and Charting in Excel |