Microsoft Office Tutorials and References
In Depth Information
Dim cell As Range
Dim cnt As Long
For Each cell In rng.Cells
If cell.Text Like pattern Then cnt = cnt + 1
Next cell
COUNTLIKE = cnt
End Function
The following formula counts the number of cells in B4:B11 that contain the letter e:
=COUNTLIKE(B4:B11,”*[eE]*”)
Counting sheets in a workbook
The following COUNTSHEETS function accepts no arguments and returns the number of sheets in the work-
book from where it's called:
Function COUNTSHEETS() As Long
COUNTSHEETS = Application.Caller.Parent.Parent.Sheets.Count
End Function
This function uses Application.Caller to get the range where the formula was entered. Then it uses two Parent
properties to go to the sheet and the workbook. Once at the workbook level, the Count property of the Sheets
property is returned. The count includes worksheets and chart sheets.
Counting words in a range
The WORDCOUNT function accepts a range argument and returns the number of words in that range:
Function WORDCOUNT(rng As Range) As Long
Count the words in a range of cells
Dim cell As Range
Dim WdCnt As Long
Dim tmp As String
For Each cell In rng.Cells
tmp = Application.Trim(cell.Value)
If WorksheetFunction.IsText(tmp) Then
WdCnt = WdCnt + (Len(tmp) – _
Len(Replace(tmp, “ “, “”)) + 1)
End If
Next cell
WORDCOUNT = WdCnt
End Function
I use a variable, tmp, to store the cell contents with extra spaces removed. Looping through the cells in the sup-
plied range, the ISTEXT worksheet function is used to determine whether the cell has text. If it does, the num-
Search JabSto ::




Custom Search