Count instances of "No"

[samuel.nunn]

Need a Macro (not formula) for the following:

How do I count the instances of the string "No" within the range B2:B65536. Also, I need to store this count as a variable so I can then post the count to another worksheet. Any suggestions?

 

[Jonmo1]

MyVariable = Application.Countif(Range("B2:B65536"),"No")

 

[stanleydgromjr]

samuel.nunn,

Option Explicit
Sub CountNo()
Dim NbrOfNo As Long
Dim rng As Range

  Set rng = Range("B2", Range("B" & Rows.Count).End(xlUp))
  
  NbrOfNo = Application.WorksheetFunction.CountIf(rng, "No")
  
  Sheets("Sheet2").Range("A1").Value = Application.WorksheetFunction.CountIf(rng, "No")

End Sub

Have a great day,
Stan

 

[samuel.nunn]

Thanks, jonmo1. Worked great.

Question though, why does it have to be "application"? Why doesn't "worksheet" work?

Thanks,
Sam

 

[Jonmo1]

Thanks, jonmo1. Worked great.

Question though, why does it have to be "application"? Why doesn't "worksheet" work?

Thanks,
Sam

Because Countif is a function, just like you would write in a cell formula.
It's part of the Excel Application, not the worksheet.

Technically speaking, the full command is

Application.WorksheetFunction.Countif

The worksheetfunction object holds all (well, almost all) the same functions that you can find in writing a formula in a cell.
But it works when you drop the WorksheetFunction and just write Application.