VBA COUNTIF SOLUTION

buvanamali

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

Board Regular
Joined
Oct 13, 2020
Messages
78
Office Version
  1. 2016
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,703
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
One of the reasons I didn't use a formula.... :giggle:
 

buvanamali

New Member
Joined
Jul 27, 2014
Messages
26
Ok,
Allow me to have what you say about this
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

Well-known Member
Joined
Oct 27, 2005
Messages
19,703
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows

ADVERTISEMENT

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

buvanamali

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

Well-known Member
Joined
Oct 27, 2005
Messages
19,703
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
If you only want the search to be limited to the Master Sheet, try
VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,456
Messages
5,596,234
Members
414,048
Latest member
wnied1

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
Top