Search code examples
excelvbacountif

How I make my count ifs function for certain worksheets work?


I've been trying to write my own code because I can't find anything that does what i need it to. First, the code:

      Dim n As Long
Dim m, range1 As Range
Dim wss As Worksheet
Set range1 = wss.Range("FC2", wss.Range("FC2").End(xlDown))

  If wss.Name Like "TAPS" Then
For Each wss In Worksheets
With range1
For Each m In .Rows
If Application.CountIf(m) > 30 Then
n = n + 1
Mark1.WBAVAILABLETEXTBOX.Value = n
End If

What I want this code to do is find every sheet in my workbook with the word "TAPS" in the name and count every used row in the "FC2" whose cell value is over 30, and put that value in the text box.

I originally had some "Next" and "end with" in the code, but it kept popping up with errors, saying not only "end with without with," but when I delete it, it says the opposite, and the same problem with "next" regarding the "for each" aspects.

This is code I have come up with using my limited knowledge, and I am even having trouble explaining it. Any ideas for what I am doing wrong?

EDIT FOR CLARITY I am hoping for the final result to be the combined total from every worksheet.


Solution

  • Assume OP tries to validate every used rows in the usedrange, column FC is used to determin the data range.

    Please provide the detail on OP or comment if I were misunderstanding it.

    Sub demo()
        Dim n As Long, i
        Dim Range1 As Range
        Dim wss As Worksheet
        n = 0
        For Each wss In Worksheets
            If wss.Name Like "*TAPS*" Then
                For i = 2 To wss.Cells(wss.Rows.Count, "FC").End(xlUp).Row
                    Set Range1 = wss.Rows(i)
                    If Application.CountIf(Range1, ">30") > 0 Then n = n + 1
                Next
            End If
        Next
        Mark1.WBAVAILABLETEXTBOX.Value = n
    End Sub