Count every word which contains a bold character - ready short code, but something is wrong (value error)

[makiwara]

Hi!

I inserted my code and added some comment. I hope that somebody can find the mistake.

Function CountBold(WorkRng As Range)


    Dim i, xcount As Integer
    For i = 1 To Len(WorkRng)




    If WorkRng.Characters(i, 1).Font.Bold = True Then
    
            If WorkRng.Characters(1, 1).Fold.Bold = True Then
            xcount = 1 ' if first character is bold then we give the value of 1 to xcount ([B]You[/B] ate an [B]apple yesterday.[/B] -->1)

            End If
            
    If WorkRng.Characters(i - 1, 1) = " " Then 'for every other word in cell we count the cases when there is a bold
'letter and a space before it --> ([B]You[/B] ate an [B][COLOR=#ff0000]a[/COLOR]pple [COLOR=#ff0000]y[/COLOR]esterday.[/B] -->1 for "you" and +2 = 3 words
    
        xcount = xcount + 1
    End If
End If


Next i


CountBold = xcount
End Function

Can you spot the mistake? (Value error)

Thank you very much

 

[JoeMo]

Does this line cause the error?

If WorkRng.Characters(i - 1, 1) = " "

Since i starts at 1, i-1 is zero, but the Characters object start value must either be omitted or have a minimum value of 1.
You might try replacing that line with this:

If WorkRng.Characters(1, 1) = " " Then
    'your code to do whatever you want when there's a leading space ...

 

[Joe4]

Also note that every time you find a bold character, you are checking the first characters. So there is a problem with your loop, as well as how you are checking for a space.

This code will do what you want:

Function CountBold(WorkRng As Range) As Integer

    Dim i As Integer, xcount As Integer
    
    For i = 1 To Len(WorkRng)
        If WorkRng.Characters(i, 1).Font.Bold = True Then
            If i = 1 Then
                xcount = 1
            Else
                If Mid(WorkRng, i - 1, 1) = " " Then xcount = xcount + 1
            End If
        End If
    Next i

    CountBold = xcount

End Function

 

[makiwara]

Thank you very much, works great

Also note that every time you find a bold character, you are checking the first characters. So there is a problem with your loop, as well as how you are checking for a space.

This code will do what you want:

Function CountBold(WorkRng As Range) As Integer

    Dim i As Integer, xcount As Integer
    
    For i = 1 To Len(WorkRng)
        If WorkRng.Characters(i, 1).Font.Bold = True Then
            If i = 1 Then
                xcount = 1
            Else
                If Mid(WorkRng, i - 1, 1) = " " Then xcount = xcount + 1
            End If
        End If
    Next i

    CountBold = xcount

End Function

 

[Joe4]

You are welcome.