CODEWITHSUNDEEP
vbaexcel
carter
carter

Reputation: 75

Count sheets if specific cell equals string

I'm trying to count each sheet for which cell A1 = "next" then store that number as a variable. Then set cell A2 on current sheet equal to that variable. Here is what I have:

Dim ws As Worksheet

i = 0
For Each ws In ActiveWorkbook.Worksheets
If range("A1").value = "next"
i = i + 1
End If
Activesheet.Range("a2").Value = i
Next ws

what am I missing here?

Upvotes: 0

Views: 65

Answers (1)

Scott Craner
Scott Craner

Reputation: 152450

You need to tell vba on which sheet Range("A1") belongs:

If ws.range("A1").value = "next" Then

So:

Dim ws As Worksheet
Dim i As Long
i = 0
For Each ws In ActiveWorkbook.Worksheets
    If ws.range("A1").value = "next"
        i = i + 1
    End If
    Activesheet.Range("A2").Value = i
Next ws

Upvotes: 2

Related Questions