Hello Andrew,?@
Hi guys, very nice approach, I add Extended to cover more than one RANGE with using ParamArray.
Something like this..
PHP Code:
=COUNTCH("a",FALSE,B1:B3,C4:C7,E1:E10)
As the HELP file says, ParamArray cannot be used with Optional keyword...so it depends on which one does Andrew want.
Regarding to the problem that Cesar wrote, why don't you divide the result by the count of text?
Option Explicit
Sub Auto_Open()
'Add discription to this UDF
Application.MacroOptions Macro:="COUNTCH", _
Description:="This is an UDF that counting characters inside a cell ", _
HasShortcutKey:=False, _
Category:=7
End Sub
Function COUNTCH(strText As String, _
bCaseSen As Boolean, _
rng As Range, _
ParamArray OtherRng()) As Long
Dim rngCell As Range, lCounter As Long, i As Long
lCounter = 0
For Each rngCell In rng
lCounter = lCounter + CountUp(rngCell, lCounter, strText, bCaseSen)
Next rngCell
For i = 0 To UBound(OtherRng)
For Each rngCell In OtherRng(i)
lCounter = lCounter + CountUp(rngCell, lCounter, strText, bCaseSen)
Next
Next
COUNTCH = lCounter
End Function
Private Function CountUp(r As Range, _
lCounter As Long, _
sTxt As String, _
bCaseSen As Boolean) As Long
If bCaseSen Then
lCounter = (Len(r.Value) - _
Len(Application.Substitute(r.Value, sTxt, ""))) / Len(sTxt)
Else
lCounter = (Len(r.Value) - _
Len(Application.Substitute(LCase(r.Value), LCase(sTxt), ""))) / Len(sTxt)
End If
CountUp = lCounter
End Function
HTH