View original (French)

VBA COUNT THE NUMBER OF OCCURRENCES

noobduvb -  
 NikoBübü -
Hello everyone!!!

Here I come to you because I can’t get a correct and fast function to find the number of occurrences in my Excel sheet!

While snooping around a bit I found this very clever function that counts the number of replacements made!

Function NbOc(Chaine As String, Ch As String) As Long
NbOc = (Len(Chaine) - Len(Replace(Chaine, Ch, ""))) / Len(Ch)
End Function

but unfortunately this function doesn’t work for me :(

if someone could help! xD

bye bye

6 answers

  1. NikoBübü
     
    I think you’d rather do:
    1) count the number of characters in the original string
    2) replace your "1" with "" using the Replace() function
    3) recount the number of characters
    4) compare the first and second counts
    you’ll thus have the number of "1" in your string

    here it is:

    Sub test()
    Dim str As String
    Dim num, num2 As Integer

    str = "abc1def1ghi1jkl113265481"
    num1 = Len(str)
    str = Replace(str, "1", "")
    num2 = Len(str)

    num1 = num1 - num2
    MsgBox ("il y a " & num1 & " fois le chiffre 1 dans la chaine")

    End Sub
    1