CODEWITHSUNDEEP
excelvba
Martin Lauf
Martin Lauf

Reputation: 33

Count special characters in range in a function

This function should count all special hidden characters in selected range.

I got an error. StrIn = C.Text didn't work.

Function hiddencount(Rng As Range)

Dim C As Range
Dim cnt As Integer
Dim iCh As Integer
Dim StrIn As String
cnt = 0

For Each C In Rng.Cells
    C.Value = StrIn

    If Not C.HasFormula Then
        For iCh = 1 To Len(StrIn)
            If Asc(Mid(StrIn, iCh, 1)) < 32 Then
                cnt = cnt + 1
            End If
        Next iCh
    Else
    End If
Next C

hiddencount = cnt

End Function

Upvotes: 1

Views: 72

Answers (1)

urdearboy
urdearboy

Reputation: 14580

In short, change C.Value = StrIn to StrIn = C.Text

You really do not need this variable though. It just creates more code to read through later. Why not just use C.Text so it is clear what is being analyzed? Also, if you do not plan on acting on the Else statement, you can just remove it. 

Function hiddencount(Rng As Range)

Dim C As Range, cnt As Integer, iCh As Integer

For Each C In Rng.Cells
    If Not C.HasFormula Then
        For iCh = 1 To Len(C.Text)
            If Asc(Mid(C.Text, iCh, 1)) < 32 Then cnt = cnt + 1
        Next iCh
    End If
Next C

hiddencount = cnt

End Function

Upvotes: 2

Related Questions