Find and count occurrances

J

jmoss

How can I count the number of times "OK" or "CA" or "NG" occur in a
column using VB? If the count of "NG" is equal to or greater than 20,
then don't print the worksheet.
 
D

Dave Peterson

Do you mean VBA or VB?

In a cell in a worksheet, I'd use:
=sum(countif(a:a,{"ok","ca","ng"})

In VBA, I'd use:

Option Explicit
Sub testme()
Dim myStr As Variant
Dim myTotal As Long
Dim iCtr As Long
Dim myRng As Range

Set myRng = ActiveSheet.Range("a:a")

myStr = Array("ok", "ca", "ng")
myTotal = 0
For iCtr = LBound(myStr) To UBound(myStr)
myTotal = myTotal + Application.CountIf(myRng, myStr(iCtr))
Next iCtr

MsgBox myTotal

End Sub
 
J

jmoss

Dave said:
Do you mean VBA or VB?

In a cell in a worksheet, I'd use:
=sum(countif(a:a,{"ok","ca","ng"})

In VBA, I'd use:

Option Explicit
Sub testme()
Dim myStr As Variant
Dim myTotal As Long
Dim iCtr As Long
Dim myRng As Range

Set myRng = ActiveSheet.Range("a:a")

myStr = Array("ok", "ca", "ng")
myTotal = 0
For iCtr = LBound(myStr) To UBound(myStr)
myTotal = myTotal + Application.CountIf(myRng, myStr(iCtr))
Next iCtr

MsgBox myTotal

End Sub

jmoss wrote:-

How can I count the number of times "OK" or "CA" or "NG" occur in a
column using VB? If the count of "NG" is equal to or greater than 20,
then don't print the worksheet.
Click to expand...

Thanks for the code, this solved my problem! This is a great forum.
 
Top