Search for text in a cell
 

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

--
Message posted from http://www.ExcelForum.com

Search for text in a cell
 

You can use COUNTIF for this:

Sub TestIt()
Dim SearchString As String

SearchString = "Gate 4"

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

--
Regards

Juan Pablo González

"morry " wrote in message
...
In column B of my spreadsheet i have descriptions that are about twenty
or so words long. I want to count the one's that contain specific text
such as "Gate 4" contained in the text. I listed the code that i have
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

Morry


---
Message posted from http://www.ExcelForum.com/

Search for text in a cell
 

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



--
Message posted from http://www.ExcelForum.com