检查工作簿VBA中的出现次数

Question

我正在尝试遍历工作簿中的许多工作表，看看是否存在foo和bar，然后添加到这个特定的单元格。出于某种原因，我的代码循环访问工作表，但不添加到单元格中。

Sub Main()
Dim ws As Worksheets 
Dim starting_ws As Worksheet
Set starting_ws = ActiveSheet 
ws_num = ThisWorkbook.Worksheets.Count
ind = 9
For I = 1 To ws_num
ThisWorkbook.Worksheets(I).Activate
Do While ind <= 39
If Worksheets(I).Range("A" & ind).Value = "bar" And Worksheets(I).Range("G" & ind).Value = "foo" Then
Worksheets("scrap").Range("C7").Value = Worksheets("scrap").Range("C7").Value + 1
Exit For
End If
ind = ind + 1
Loop
Next
End Sub

Answer 1

也许

Sub tgr()
Dim ws As Worksheet
Dim rOutput As Range
Dim lCount As Long
Set rOutput = ThisWorkbook.Sheets("scrap").Range("C7")
For Each ws In ThisWorkbook.Worksheets
lCount = lCount + WorksheetFunction.CountIfs(ws.Range("A9:A39"), "bar", ws.Range("G9:G39"), "foo")
Next ws
rOutput.Value = rOutput.Value + lCount
End Sub

Answer 2

试试这个：

Sub Main()
Dim ws As Worksheets 
Dim starting_ws As Worksheet
Set starting_ws = ActiveSheet 
ws_num = ThisWorkbook.Worksheets.Count
For I = 1 To ws_num
ind = 9
ThisWorkbook.Worksheets(I).Activate
Do While ind <= 39
If Worksheets(I).Range("A" & ind).Value = "bar" And Worksheets(I).Range("G" & ind).Value = "foo" Then
Worksheets("scrap").Range("C7").Value = Worksheets("scrap").Range("C7").Value + 1
Exit Do
End If
ind = ind + 1
Loop
Next
End Sub