Count word even if other words are in same line help

[Patriot2879]

Hello good evening hope you can help me please? I have the code below which works great and it counts the word ‘CAG’. But it doesn’t count if their is another word before or after it. For example ‘cancelled cag’ or ‘cag replanned’. Hope you can help please.

Private Sub userform_initialize()
Dim j As Long
j = Application.WorksheetFunction.CountIf(ThisWorkbook.Sheets("In Day VL").Range("A:A"), "CAG",)
 UserForm1.TextBox2.Value = j
End Sub

 

[Micron]

Use wild cards?

Using wildcard in vba countif function

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"...

www.mrexcel.com

 

[Patriot2879]

Use wild cards?

Using wildcard in vba countif function

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"...

www.mrexcel.com

Hi i have no idea what a wild card is sorry, I am a beginner in vba

 

[Micron]

Did you follow the link and read the CountIf solution? You are able to research suggestions you don't understand, yes?

You will likely only ever need one of the first 3 in this list

Wildcard Characters used in String Comparisons

Office VBA reference topic

learn.microsoft.com

Give it a try.

 

[Patriot2879]

Hi i have no idea what a wild card is sorry, I am a beginner in vba

Hi i have just tried editing the code to the below but it still didnt work, hope you can help please?

Private Sub userform_initialize()
Dim j As Long
Dim occurance As Integer
j = Application.WorksheetFunction.CountIf(ThisWorkbook.Sheets("In Day VL").Range("A:A"), "CAG")
occurance = WorksheetFunction.CountIf(Cells, "*" & Search & "*")
End Sub

 

[Patriot2879]

Did you follow the link and read the CountIf solution? You are able to research suggestions you don't understand, yes?

You will likely only ever need one of the first 3 in this list

Wildcard Characters used in String Comparisons

Office VBA reference topic

learn.microsoft.com

Give it a try.

Hi I have tried several codes now still cant get it to work. Hope you can help me please?

 

[Micron]

Your example uses no wild cards. Did you read the solution at the link or not?
Try
j = Application.WorksheetFunction.CountIf(ThisWorkbook.Sheets("In Day VL").Range("A:A"), "*CAG*")

I see no reason to concatenate the wild cards in the given example there, unless one wants to use a variable instead of the literal "CAG"

 

[Patriot2879]

Your example uses no wild cards. Did you read the solution at the link or not?
Try
j = Application.WorksheetFunction.CountIf(ThisWorkbook.Sheets("In Day VL").Range("A:A"), "*CAG*")

I see no reason to concatenate the wild cards in the given example there, unless one wants to use a variable instead of the literal "CAG"

That works a treat thank you cant believe it was just a '*' infront the cag and after, i did try other things but didnt fully understand the wild card.