How to create VBA code to VBA for counting specific texts from different ranges

How to create VBA code to count specific texts from different ranges

Requirement:

The user has an worksheet that having different categories and needs a VBA for counting the texts by category wise. Following are the details:

The user A raw with the categories A,B,C, D, E , F and i have the texts like approved, rejected, hold etc... in raw number H. The user will have to count these texts by category and texts in the second worksheet like below:

Categoury	A	B	C
Approved	3	3	1
Rejected	1	1	2
Hold	0	0	1

https://www.ozgrid.com/forum/forum/help-forums/excel-vba-macros/1199399-vba-for-counting-specific-texts-from-different-ranges

Solution:

Copy and paste the following macro into the worksheet code module. Do the following: right click the tab for your "Worksheet" sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Change any value in column A or column H and exit the cell and the "Summary" sheet will update automatically. If you want to trigger the macro by only changing column A and not column H, then revise the range: Range("A:A,H:H").

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A:A,H:H")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Sheets("Summary").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim bottomH As Long
    bottomH = Sheets("Worksheet").Range("H" & Rows.Count).End(xlUp).Row
    Dim status As Range
    Dim cat As Range
    For Each status In Sheets("Summary").Range("A2:A" & LastRow)
        Sheets("Worksheet").Range("A1:H" & bottomH).AutoFilter Field:=8, Criteria1:=status
        For Each cat In Sheets("Summary").Range("B1:G1")
            Sheets("Worksheet").Range("A1:H" & bottomH).AutoFilter Field:=1, Criteria1:=cat
            Sheets("Summary").Cells(status.Row, cat.Column) = Sheets("Worksheet").AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Cells.Count - 1
        Next cat
    Next status
    If Sheets("Worksheet").AutoFilterMode = True Then Sheets("Worksheet").AutoFilterMode = False
    Application.ScreenUpdating = True
End Sub

Obtained from the OzGrid Help Forum.

Solution provided by Mumps.