Search for text in a cell

M

morry

In column B of my spreadsheet i have descriptions that are about twent
or so words long. I want to count the one's that contain specific tex
such as "Gate 4" contained in the text. I listed the code that i hav
made so far. Could somebody tell me what is wrong with my code.

Sub Count_Gate()

Dim Gate1 As Integer
Dim Gate2 As Integer
Dim Gate4 As Integer

Gate1 = 0
Gate2 = 0
Gate4 = 0

Do
If Columns("B:B") = "Gate 1" Then
Gate1 = Gate1 + 1
End If

Loop Until Range("B:B").End(xlUp).Row

Thank you

Morr
 
J

Juan Pablo González

You can use COUNTIF for this:

Sub TestIt()
Dim SearchString As String

SearchString = "Gate 4"

MsgBox Application.CountIf(Range("B:B"), "*" & SearchString & "*")
End Sub
 
K

kkknie

Your code looks for the entire cell being the searched value, try:

Sub Count_Gate()

Dim r As Range
Dim Gate1 As Integer
Dim Gate2 As Integer
Dim Gate4 As Integer

Gate1 = 0
Gate2 = 0
Gate4 = 0

For Each r in Range("B1:B" & Range("B65536").End(xlUp).Row)
If Instr(1, r.Value, "Gate 1") <> 0 Then Gate1 = Gate1 + 1
Next

End Sub
 
Top