VBA COUNTIF SOLUTION

buvanamali

New Member
Joined
Jul 27, 2014
Messages
37
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
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
Ok, whose code are you referring to?
 
Upvote 0
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
 
Upvote 0
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?.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,700
Members
448,293
Latest member
jin kazuya

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top