vba 计算具有特定文本的行数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18285532/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Count number of rows with specific text
提问by user2634936
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.
即使其中一行中确实存在“Orange”，我的代码也会将所有值返回为“0”。
回答by Graham Anderson
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*".
如果您需要计算字符串中包含单词的任何行，您可以将 countif 函数中的字符串从 "Orange" 修改为"*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*").
CountIf(Range("M1:M100"), "Orange")但会由CountIf(Range("M1:M100"), "*Orange*").
回答by user2634936
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
我需要做的基本上是为您要引用的工作表设置一个变量，在我的情况下，它将是“IN PROG”并在声明范围时调用该变量。我在上面发布的代码是正确的，但是它不是在源 excel 表中查看文本“橙色”，而是在我执行 VBA 的 excel 文件中找到文本值。希望能帮助到你
回答by user5314602
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
