VBA COUNTIF SOLUTION

[buvanamali]

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

 

[hnsd24_CN]

For other members tracking this thread, one thing should be noted:
"List errors"provided by @buvanamali,Cells that look blank are not really blank.They may contain invisible characters or space or formulas. That's why I used Countif instead of CountA

 

[Michael M]

One of the reasons I didn't use a formula....

 

[mohadin]

Ok,
Allow me to have what you say about this

Test file.xls

Shared with Dropbox

www.dropbox.com

 

[buvanamali]

Ok,
Allow me to have what you say about this

Test file.xls

Shared with Dropbox

www.dropbox.com

Dear Mr Mohadin
I am not an VBA expert. After seeing the post 31 by Mr hnsd24_CN regarding the data I posted may contain invisible characters or space or formulas. That's why I used Countif instead of CountA. I was trying to clean my data with Mr Jindon's macro also. But I had no frutiful result. But your macro seems to be close to my request and I will back to you after applying with few more test.

I appreciate the iniatives and efforts taken by you with sincere gratitude.

I get apoligies from the experts, still I can not challenge your efforts. Everyone's effort give its their expertise result. But I will not say that none of your help proved futile.

I appreciate the everybody's help by providing through macro, you are giving me an opportunity to learn further more.

I am not under estimating others proven knowledge and expertness.

buvanamali

 

[Michael M]

Did you retry with my code in post #2 AFTER you had modified your data list ??

 

[buvanamali]

Did you retry with my code in post #2 AFTER you had modified your data list ??

Dear Mr Michael M

Yesterday I tried your macro after modifying / removing additional spaces and removing the "/""\" chrs, I got the result of total errors. May I request you to modify the macro to confine to master sheet only. Because the other sheets parsed data of master sheet.
Thank you advance.

With regards.

buvanamali

 

[Michael M]

If you only want the search to be limited to the Master Sheet, try

Sub MM1()
Dim ws As Worksheet, cell As Range, c As Integer, r As Long, lr As Long
c = 0
Set ws = Sheets("Master")
For r = 2 To 14
    With ws
        lr = ws.Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row
        For Each cell In .Range("E1:Q" & lr)
                If cell.Value = Sheets("Error List").Range("A" & r).Value Then
                    c = c + 1
                End If
        Next cell
    End With
Sheets("Error List").Range("B" & r).Value = c
c = 0
Next r
End Sub