VBA COUNTIF SOLUTION

buvanamali

New Member
Joined
Jul 27, 2014
Messages
18
Hi

The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per sheet.
I am in search of a macro to count the no of instances of the following errors in each column and the number of counts result at the end of last row of respective column of each sheet.
My data starts at from 2nd row, 1st row being the header on each sheet. The number of rows per sheet will vary. Can anyone help me in resolving this issue.
Thanks in Advance.


1) Card Reader Error
2) Cash Handler Error
3) All Cassettes Down/Fatal
4) Local/Communication Error
5) Exclusive Local Error
6) Cashout Error
7) In Supervisory
8) Closed
9) Encryptor Error
10) Reject Bin Error
11) All Cassettes Down/Fatal Admin Cash
12) Cash Acceptor Faults
13) AB Full/Reject bin Overfill


Buvanamali
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,391
Office Version
  1. 2013
Platform
  1. Windows
I created a Sheet called "Error List" and put the list of errors in col "A", while removing the number, bracket and space from each line.
The run the following code. It will populate column "B" with each error
VBA Code:
Sub MM1()
Dim ws As Worksheet, cell As Range, c As Integer, r As Long
c = 0
For r = 2 To 14
    For Each ws In Worksheets
        If ws.Name <> "Error List" Then
            For Each cell In ws.Range("E1:Q500")
                    If cell.Value = Sheets("Error List").Range("A" & r).Value Then
                        c = c + 1
                    End If
            Next cell
        End If
    Next ws
Sheets("Error List").Range("B" & r).Value = c
c = 0
Next r
End Sub
 

hnsd24_CN

Board Regular
Joined
Oct 13, 2020
Messages
78
Office Version
  1. 2016
Platform
  1. Windows
no test,put on workbook
VBA Code:
Sub total_err()
    Dim ws As Worksheet
    Dim rng As Range
    For Each ws In Worksheets
        For Each rng In ws.Range("E1:Q1")
            ws.Cells(Application.CountA(rng.EntireColumn) + 1, rng.Column) _
            = Application.CountA(rng.EntireColumn) - 1
        Next
    Next
End Sub
 

buvanamali

New Member
Joined
Jul 27, 2014
Messages
18
Hi

Thanks a lot for your kind and immediate response. But I could not get it resolved. However I enclose a file with my anticipated result for your kind perusal.
Now you are able to understand my requirement with this sample file.

Request for a solution with advance thanks.

buvanamali
TestFile.jpg
 

hnsd24_CN

Board Regular
Joined
Oct 13, 2020
Messages
78
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Hi

Thanks a lot for your kind and immediate response. But I could not get it resolved. However I enclose a file with my anticipated result for your kind perusal.
Now you are able to understand my requirement with this sample file.

Request for a solution with advance thanks.

buvanamaliView attachment 24890
What happens when you run my code?
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,391
Office Version
  1. 2013
Platform
  1. Windows
Ok, whose code are you referring to?
 

buvanamali

New Member
Joined
Jul 27, 2014
Messages
18

ADVERTISEMENT

What happens when you run my code?
HI
While running both the codes it results in total no of counts i.e. total no of rows reflected as result. I expect only count with data error details totals only.

Waiting for your solution

Thanks

buvanamali
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,391
Office Version
  1. 2013
Platform
  1. Windows
My code counts up each error on all sheets then puts a total for each error o the error list sheet, next to each error name. Do you follow my instructions as provided?.
 

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
612
Office Version
  1. 2013
Platform
  1. Windows
What about
VBA Code:
Sub test()
    With ActiveSheet
        Glr = .Cells(Rows.Count, 1).End(xlUp).Row
        For i = 1 To 13
            fr = .Cells(2, i + 4).End(xlDown).Row
            lr = .Cells(Rows.Count, i + 4).End(xlUp).Row
            If fr < lr Then
                x = lr - fr + 1
            Else
                x = 0
            End If
            Cells(Glr + 1, i + 4) = x
        Next
    End With
End Sub
 

buvanamali

New Member
Joined
Jul 27, 2014
Messages
18
What about
VBA Code:
Sub test()
    With ActiveSheet
        Glr = .Cells(Rows.Count, 1).End(xlUp).Row
        For i = 1 To 13
            fr = .Cells(2, i + 4).End(xlDown).Rowesu
            lr = .Cells(Rows.Count, i + 4).End(xlUp).Row
            If fr < lr Then
                x = lr - fr + 1
            Else
                x = 0
            End If
            Cells(Glr + 1, i + 4) = x
        Next
    End With
End Sub
Hi

It does nothing. No expected results. In the example I had used countif formlua and arrived at a solution. But it will not be good for more than 200 sheets with more than 500 rows. Hence waiting for a fruitful solution.

buvanamali
 

Watch MrExcel Video

Forum statistics

Threads
1,113,833
Messages
5,544,574
Members
410,621
Latest member
S Oberlander
Top