Count number of rows which contain specific text

[taykaisheng]

Hi, I am on an assignment to count the number of rows which contain specific text and print the value on email. However I'm stuck here

Here are my code

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 = "orange@aod.au"
    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 current code will return all the value as "0" eventhough "Orange" does exist in one of the row. Hope someone could help me to solve the problem. Thank You

 

[Rick Rothstein]

Hi, I am on an assignment to count the number of rows which contain specific text and print the value on email. However I'm stuck here

Here are my code

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 = [B][COLOR=#a52a2a]Application.WorksheetFunction.CountIf(Range("M1:M100"), "Orange")[/COLOR][/B]
   
    Source.Close SaveChanges:=False
    
    Set Source2 = Workbooks.Open("C:\Users\HP\Desktop\SR INPROG.xlsx")
    
    Var10 = [B][COLOR=#a52a2a]Application.WorksheetFunction.CountIf(Range("M1:M100"), "Orange")[/COLOR][/B]
    
    Source2.Close SaveChanges:=False
    
    eTo = "orange@aod.au"
    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 current code will return all the value as "0" eventhough "Orange" does exist in one of the row. Hope someone could help me to solve the problem. Thank You

Try changing the red highlighted parts to this...

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

Note the asterisks (wildcard symbols) around the word "Orange".

 

[taykaisheng]

Thanks for your quick reply, but unfortunately it doesn't work, not sure what's the issue

 

[Rick Rothstein]

Thanks for your quick reply, but unfortunately it doesn't work, not sure what's the issue

Please describe "doesn't work" for us.

 

[taykaisheng]

Please describe "doesn't work" for us.

The return value will always be "0" no matter what, even when there are 2 rows on the worksheet with "Orange" specified.