CODEWITHSUNDEEP
excelvbascripting
barskyn
barskyn

Reputation: 383

Checking number of Occurrences In Workbook VBA

I am trying to iterate through many worksheets in a workbook and see if foo and bar are present then add to this specific cell. For some reason my code iterates through the worksheets, but does not add to the cell.

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

Upvotes: 1

Views: 281

Answers (2)

tigeravatar
tigeravatar

Reputation: 26660

Perhaps

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

Upvotes: 2

DirtyDeffy
DirtyDeffy

Reputation: 507

Try this:

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

Upvotes: 1

Related Questions