count occurrence of string within string using VBA

[almagg]

i want to count the number of times a string appears in a string.
sy for example "Excel"

i thought this would work but i get an 'Object Required' when it starts
and 'CountIf' is highlighted.

Dim myfile
Dim mycount

myfile = InputBox("Enter the file name.")

mycount = WorksheetFunction.CountIf(myfile, "*Excel*")
MsgBox "The text 'Excel' appeared" & mycount & "times in the file name."

 

[Scott Huish]

GetOpenFileName is a better way to get a filename, but to answer your question:

Sub test()
Dim myfile As String
Dim mycount As Long

myfile = InputBox("Enter the file name.")
mycount = UBound(Split(myfile, "Excel"))
MsgBox mycount

End Sub

 

[Weaver]

try this

Sub thing()
    str1 = "excel"
    str2 = "My Excel skills are excellent"
    MsgBox (Len(str2) - Len(Replace(LCase(str2), LCase(str1), ""))) / Len(str1)
End Sub

 

[almagg]

both worked well
thanks

and yeah GetOpenFileName is good