VBA count number of periods

[gailb]

I have a long text string and want to count the number of periods in the first part. I'm using the Split to separate the first part to count the periods.

I get A1.1. and I'd like to show the count of two periods as I'm going to apply some format around the cells that have a certain number of periods.

MyCnt returns 0

Code: Select all

Sub CountPeriod()
    Dim i As Long
    Dim LastRow As Long: LastRow = Range("A" & Rows.Count).End(xlUp).Row
    Dim MyText As String
    Dim MyCnt As Long
    For i = 1 To LastRow
        MyText = Split(Range("A" & i), " ")(0)
        MyCnt = WorksheetFunction.CountIf(Range("A" & i), ".")
    Next i
End Sub

[HansV]

The COUNTIF function returns the number of cells in a range that satisfies a certain condition; you cannot use it to count characters within a single string.
Use this instead:

Code: Select all

        MyCnt = Len(MyText) - Len(Replace(MyText, ".", ""))

[gailb]

Thanks again Hans. As I continued my search, I found this which also works well.

Code: Select all

MyCnt = UBound(Split(Split(Range("A" & i), " ")(0), "."))