Count If Macro

[ajamesf89]

I can not figure out a multiple count if macro. Basically i want it to search a column (read d2:d3000) and find a set of letters such as "CB", "KP2" and throw out all the counts. I tried to record the macro then put multiple lines in but this did not work. can someone assist?

below is my example
Sub WeeklyCountIfs()
'
' WeeklyCountIfs Macro
'

'
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-2009]C4:R[989]C4,""CB"")"
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-2009]C4:R[989]C4,""PRT"")"
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-2009]C4:R[989]C4,""CCC"")"
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-2009]C4:R[989]C4,""DEM"")"
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-2009]C4:R[989]C4,""DEL"")"
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-2009]C4:R[989]C4,""KGD"")"
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-2009]C4:R[989]C4,""RC"")"
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-2009]C4:R[989]C4,""RW"")"
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-2009]C4:R[989]C4,""PH"")"
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-2009]C4:R[989]C4,""KPH"")"
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-2009]C4:R[989]C4,""KCC"")"
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-2009]C4:R[989]C4,""KDM"")"
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-2009]C4:R[989]C4,""KDL"")"
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-2009]C4:R[989]C4,""UGD"")"
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-2009]C4:R[989]C4,""KRC"")"
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-2009]C4:R[989]C4,""PM"")"
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-2009]C4:R[989]C4,""PM1"")"
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-2009]C4:R[989]C4,""PM2"")"
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-2009]C4:R[989]C4,""KPM"")"
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-2009]C4:R[989]C4,""KP2"")"
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-2009]C4:R[989]C4,""KP1"")"

End Sub

 

[Fluff]

Try

ActiveCell.FormulaR1C1 = "=COUNTIF(R2C4:R[-1]C4,""CB"")"

 

[ajamesf89]

this did work thank you! anyway to make it so it counts all of my lines and just spaces them out by a comma or labels them for me? I dont want it to count based off two criteria, such as count if it meets this but it also has to meet this as well to count as 1. I just want counts for all those singly because i am looking through 2500 plus lines at a time.

 

[Fluff]

How about

Sub MyCountif()
   Dim Ary As Variant
   Dim Msg As String
   Dim i As Long
   
   Ary = Array("CB", "PRT", "CCC")
   For i = 0 To UBound(Ary)
      Msg = Msg & Application.CountIf(Range("D:D"), Ary(i)) & " " & Ary(i) & vbLf
   Next i
   MsgBox Msg
End Sub

Just add the rest of your values to the array