Using wildcard in vba countif function

[carl.hayes]

I am trying to return how many cells contain a string of text as entered by the user but I am struggling to put wildcards around my search term. Below is the code I have so far.

Can anyone help me with this?

Dim search as String
Dim occurance As Integer

search = InputBox("Search for", "Search Term")
occurance = WorksheetFunction.CountIf(Cells, search)

MsgBox (occurance)

 

[VoG]

Try

Dim search As String
Dim occurance As Integer

search = InputBox("Search for", "Search Term")
occurance = WorksheetFunction.CountIf(Cells, "*" & search & "*")

MsgBox occurance

 

[carl.hayes]

Thanks VoG,
It seems like I was almost there but had missed the spaces either side of the & which meant it didn't work.

You have saved me from going totally crazy trying to figure this out.