How to create VBA code to count specific texts from different ranges
Category: [Excel] Demo Available
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 |
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.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to create a macro to copy and paste in the next empty row |
| How to use VBA code to compare two different sheets in a workbook |
| How to use VBA code to copy rows from one sheet to another excluding empty rows |
| How to use VBA code to print out an area |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.