PLEASE HELP! EXCEL QUESTION [Solved/Closed]

Report
Posts
1
Registration date
Friday September 4, 2009
Status
Member
Last seen
September 5, 2009
-
 Blocked Profile -
Hello,

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


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
Next
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
http://www.vb6.us/tutorials/vb6-string-functions
Posts
7080
Registration date
Saturday April 7, 2007
Status
Anonymous
Last seen
September 23, 2020
498
Hello,

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

Best regards

hye thanks for the info :)