Count number of rows with specific text

Question

I have an assignment to count the number of rows which contain specific text and print the value on email.

Public Function First()
    Dim Source As Workbook
    Dim Var1 As Integer
    Dim Var10 As Integer
    Dim Source2 As Workbook

    Set Source = Workbooks.Open("C:\Users\HP\Desktop\IN INPROG.xlsx")

    Var1 = Application.WorksheetFunction.CountIf(Range("M1:M100"), "Orange")

    Source.Close SaveChanges:=False

    Set Source2 = Workbooks.Open("C:\Users\HP\Desktop\SR INPROG.xlsx")

    Var10 = Application.WorksheetFunction.CountIf(Range("M1:M100"), "Orange")

    Source2.Close SaveChanges:=False

    eTo = "[email protected]"
    esubject = Format(Date, "d/mmmm/yyyy") & " " & "Weekly Open Incident Reminder"
    ebody = "Dear All," & vbCrLf & "" & vbCrLf & "" & vbCrLf & "Orange: " & "SR: " & Var10 & " IN: " & Var1 

    Set app = CreateObject("Outlook.Application")
    Set itm = app.createitem(0)
    On Error Resume Next
    With itm
        .Subject = esubject
        .To = eTo
        .body = ebody
        .display

My code will return all the values as "0" even though "Orange" does exist in one of the rows.

Answer 1

If you need to count any row that contains the word within a string you could modify the string in the countif function from "Orange" to "*Orange*".

The * wildcard represents any number of characters. E.g. a cell with the string "Three orange balloons" will not be counted by

CountIf(Range("M1:M100"), "Orange") but will be by CountIf(Range("M1:M100"), "*Orange*").

Answer 2

Thanks for your solution, I have found an answer for my question and I will post it here

Set Source = Workbooks.Open("C:\Users\itsm-student\Downloads\IN INPROG.xlsx")
Set Wks = Source.Worksheets("IN INPROG")

Var1 = Application.WorksheetFunction.CountIf(Wks.Range("M1:M100"), "*Orange*")

What I need to do is basically set a variable for the worksheet that you gonna refer to and in my case it will be "IN PROG" and call the variable while declaring the range. And the codes that i posted on top are right however instead of looking the text "orange" at the source excel sheet, it find the text value on the excel file which I do my VBA on. Hope it helps

Answer 3

I did:

Sub Worksheet_Change(ByVal Target As Range)

Set Source = ThisWorkbook

Dim Var1 As Integer

Set Wks = Source.Worksheets("Recebimento")

Var1 = Application.WorksheetFunction.CountIf(Wks.Range("U:U"), "*NOK*")

If Var1 > 0 Then
 'your code

End sub