drummo87 Posts 1 Registration date Friday September 4, 2009 Status Member Last seen September 5, 2009 - Sep 5, 2009 at 10:15 AM
 Blocked Profile - Sep 6, 2009 at 12:19 PM

I am trying to figure out how to count the number of cells that contain a certain text. For example:

Column A
2007 Entry Draft
2009 Entry Draft
Undrafted Free Agent
Free Agent
Trade with Florida
Free Agent
Trade with Boston

Now I want to count how many cells contain the words "Entry Draft" out of my range A1:A7 but I do not know how to do it if they contain different other text in the same cell! PLEASE HELP!!

Thank you!

3 replies

Blocked Profile
Sep 5, 2009 at 02:52 PM
Sub test()
Dim n, p As Integer
p = 0
For n = 1 To 7
If InStr(Cells(n, 1).Text, "Entry Draft") > 0 Then
p = p + 1
End If
MsgBox (p & " Entries have been found")
End Sub

the function Instr return a long that correspond to the position of the string if it has been found, if it has been found the value is greater than 0

i suggest you learn a bit on this site
aquarelle Posts 7118 Registration date Saturday April 7, 2007 Status Moderator Last seen July 29, 2022 491
Sep 6, 2009 at 02:50 AM

It is not necessary to use VBA to do that, just use the following formula : =COUNTIF(A1:A7,"*Entry Draft*")

Best regards
Blocked Profile
Sep 6, 2009 at 12:19 PM
hye thanks for the info :)