I have many excel files with multiple sheets and each excel sheet has many formula which are starting from perticular word e.g. FDS, FDSB, etc some formula has FDS, FDSB occur in the middle of the formula. i need to find out how many times FDS,FDSB has been appear in the sheet(total count) below is the formulae for your referance =IFERROR(FDSB($D21, "IC_ESTIMATE_DATE(ALL, EXP_RPT,QTR, 3, 0, , , 'MM/DD/YYYY')@RC_ESTIMATE_DATE(ALL, EXP_RPT, QTR, +3, 0, , , 'MM/DD/YYYY')"), "na")
in such a way there are many formulas in all the cells i just need to count the how many times FDSB occur in one perticular sheet
The search string is in cell C2. Click the "start" box and the result is shown in cell c3.
In the example above, the macro counts how many times the text string "COUNTIF(" is found on the current sheet. 18 is the result. (There are 18 formulas in cell range F7:F24.)
I wanted to quickly select all cells containing formulas on a sheet. I found Rick Rothstein (MVP - Excel) macro here and modified it.
Dim Rng As Range, C As Range, Cnt As Double, Scnt As Double
On Error Resume Next
Set Rng = Cells.SpecialCells(xlFormulas)
On Error GoTo 0
If Not (Rng Is Nothing) Then
For Each C In Rng
Cnt = (Len(C.Formula) - Len(Replace(C.Formula, .Range("C2"), ""))) / Len(.Range("C2"))
Scnt = Scnt + Cnt
Worksheets("Sheet1").Range("C3").Value = Scnt