How to count occurances of a specific character in a range?

**Francium** · 06-14-2010, 06:11 PM

Hello everyone,
Thanks for all the great help on these forum, I've been reading lots and learning from all the great responses. This one seems to elude me.

I have named a set range equal to cell B8. I want to change the range if cell b8 contains only one hyphen and keep that range if it has 2 and warn if it has more than 2 hyphens.

so B8 looks usually like " 10-123-01" or "10-123"
Here is what the pseudo code looks like:

Set xlWkb = XL.Workbooks.Open(varFile) 'opening the workbook to upload
sheet = "Corr-Score" ' setting the sheet with the data needed
Set xlWks = xlWkb.Worksheets(sheet)
Set xlTestNoRng = xlWks.Range("B8")

If xlWks.Range.value *has 2 "-" then leave range as "B8"
If xlWks.Range.Value *has 1 "-" then change range to B6"

**rylo** · 06-14-2010, 06:56 PM

Hi

Broadly you could do something like

cntr = Len(Range("B8")) - Len(WorksheetFunction.Substitute(Range("B8"), "-", ""))

  Select Case cntr
    Case 1
      'reset the named range
    Case 2
      'do nothing
    Case Else
      MsgBox "You have " & cntr & " hyphens"
    End Select

This will allow you to set it if it is 1, do nothing if it is 2 or give a warning if it is > 2. You may also want to cover the situation where you have no hyphens in the result.

HTH

rylo

**Marcol** · 06-14-2010, 07:04 PM

Or

EDITED POST shg woke me up

Option Explicit

Function CountInstances(FindWhat As String, rng As Range) As Integer
    Dim PositionOfChar As Integer
    Dim HowManyChars As Integer
    
    HowManyChars = 0
    
    PositionOfChar = InStr(1, rng.Value, FindWhat)
    Do
        If PositionOfChar > 0 Then
            CountInstances = CountInstances + 1
            PositionOfChar = InStr(PositionOfChar + 1, rng.Value, FindWhat)
        Else
            Exit Do
        End If
    Loop
End Function

Or better still with shgs' suggestion

Function CountInstances(FindWhat As String, rng As Range) As Integer
    CountInstances = Len(rng.Value) - Len(Replace(rng.Value, FindWhat, ""))
End Function

Called with

NumberOfHyphens = CountInstances("-", Range("B8"))

or in the worksheet as a UDF

=CountInstances("-", A1)

The code is now a bit more flexible it will count any instances of a string in a string.

=CountInstances("?", A1)

So your code could be on these lines.

Sub YourProcedure()
    Dim FileFullName As String, wsName As String
    Dim xlWkb As Workbook
    Dim xlWks As Worksheet
    
    FileFullName = "K:\Excel Forum\TempOneOffs\Book3.xls"
    
    Set xlWkb = Workbooks.Open(FileFullName) ' opening the workbook to upload
    wsName = "Corr-Score"                    ' setting the sheet with the data needed
    Set xlWks = xlWkb.Worksheets(wsName)
    With xlWks
        Select Case CountInstances("-", .Range("B8"))
            Case 2
                .Range("B8") = .Range("B8")
            Case 1
                .Range("B8") = .Range("B6")
            Case Else
                'do something else
        End Select
    End With
End Sub

**shg** · 06-14-2010, 07:46 PM

Another way:

Sub main()
    Dim r As Range
    
    Set r = x(Range("B8"))
End Sub

Function x(r As Range) As Range
    With r(1)
        Select Case Len(.Value) - Len(Replace(.Value, "-", ""))
            Case 1: Set x = .Parent.Range("B6")
            Case 2: Set x = .Cells
            Case Else: MsgBox "Oops"
        End Select
    End With
End Function