CODEWITHSUNDEEP
vbamatchlookupcountif
aji
aji

Reputation: 59

vba countif on multiple sheets

please find the below code and comments in between

Sub Test()

Dim wSheet As Worksheet
Dim myempid As Variant

For Each wSheet In Worksheets

    Select Case (wSheet.Name)

        Case "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"

        empid2 = Worksheets("DB").Range("C13").Value2

        With Worksheets(wSheet.Name)

           'locate the row for the value
            myempid = Application.Match(empid2, .Columns("D"), 0)

            If IsError(myvalueRow) Then
                Debug.Print "empid not found in column D"
                Exit Sub
            End If

            If myempid >= 0 Then
                MsgBox ("Hi")

            '***

'(instead of the message box , i need to count the value "PL" from ("F5:AJ500") for example if myempid is in a _ col range("D8") of sheet jan , we need to count from (F8:AJ8). like wise all the values of myempid should be count in each and every sheets._ 'the sum total of count (PL) in all sheets matching the myempid should be displayed in a msgbox.

            End If
      End With

      Case Else

    End Select

Next wSheet

End Sub

Upvotes: 1

Views: 2153

Answers (1)

ashleedawg
ashleedawg

Reputation: 21619

You can use Range . Column to return the column number of a cell from a range object. Also you can use Application.WorksheetFunction.CountIf to count cells in a range that contain a certain value.

I'm not 100% clear on what you're trying to do, but based on your example, if you have a specific cell reference (for example, in an object like MyCell below), you could adapt this:

Sub myCountIf()
    Dim myCell As Range
    Set myCell = Range("D8")
    MsgBox Application.WorksheetFunction.CountIf(Sheets("DB").Range("F" & myCell.Column & ":AJ" & myCell.Column), "PL")
End Sub

Here's more info on the WorksheetFunction.CountIf Method.

Upvotes: 1

Related Questions