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
Please reply to the results of the code run and attach a screenshot
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(ws.UsedRange.Rows.Count + 1, rng.Column) _
            = Application.CountA(rng.EntireColumn) - 1
        Next
    Next
End Sub
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

buvanamali

New Member
Joined
Jul 27, 2014
Messages
26
Please reply to the results of the code run and attach a screenshot
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(ws.UsedRange.Rows.Count + 1, rng.Column) _
            = Application.CountA(rng.EntireColumn) - 1
        Next
    Next
End Sub
Hihh
Sorry to reply that the result shown by your macro is highlighted in RED . The yellow highlight shows the result of countif formula used one. The Screen shot is enclosed. Hope for the anticipated result.

Thanks in advance.

Buvanamali
TestFile.png
 

hnsd24_CN

Board Regular
Joined
Oct 13, 2020
Messages
78
Office Version
  1. 2016
Platform
  1. Windows
VBA Code:
Sub total_err()
    Dim ws As Worksheet
    Dim rng As Range
    Dim r As Integer
    For Each ws In Worksheets
        r = ws.UsedRange.Rows.Count + 1
        For Each rng In ws.Range("E1:Q1")
            ws.Cells(r, rng.Column) _
            = Application.CountIf(rng.EntireColumn, Replace( _
            ws.Cells(1, rng.Column), " ", ""))
        Next
    Next
End Sub
 

buvanamali

New Member
Joined
Jul 27, 2014
Messages
26
VBA Code:
Sub total_err()
    Dim ws As Worksheet
    Dim rng As Range
    Dim r As Integer
    For Each ws In Worksheets
        r = ws.UsedRange.Rows.Count + 1
        For Each rng In ws.Range("E1:Q1")
            ws.Cells(r, rng.Column) _
            = Application.CountIf(rng.EntireColumn, Replace( _
            ws.Cells(1, rng.Column), " ", ""))
        Next
    Next
End Sub
Hi
Thanks a lot. It is working as I expected. Shall I ask you to do few more things as I am not an VBA expert. Your code to start a new sheet append all the errors sheetwise with list of count of errors. If you reqyire specimen sheet I will put it. Again Advance thanks.
Buvanamali
 

hnsd24_CN

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

ADVERTISEMENT

Hi
Thanks a lot. It is working as I expected. Shall I ask you to do few more things as I am not an VBA expert. Your code to start a new sheet append all the errors sheetwise with list of count of errors. If you reqyire specimen sheet I will put it. Again Advance thanks.
Buvanamali
yes,I need specimen sheet to get some information, such as new sheet name, rows and columns number
 

buvanamali

New Member
Joined
Jul 27, 2014
Messages
26
yes,I need specimen sheet to get some information, such as new sheet name, rows and columns number
Ok - I am trying to post an sample wb which will have everything you asked. Here I would like to mention that all columns except the following columns are ok. The following columns G, H, O, P, Q columns are having "/" symbol, hence the problem.
I posting the following wb link
Login - Dropbox
 

hnsd24_CN

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

ADVERTISEMENT

Ok - I am trying to post an sample wb which will have everything you asked. Here I would like to mention that all columns except the following columns are ok. The following columns G, H, O, P, Q columns are having "/" symbol, hence the problem.
I posting the following wb link
Login - Dropbox
Sorry, I can't open that link. I just need a screenshot.

Due to my limited English level, I may have misunderstood your intention. I think you want to create a new sheet, sum count of each errors (from all sheets)

Is that so?
test.xlsm
AB
1errtotal
2Card Reader Errorfrom all sheets
3Cash Handler Error
4All Cassettes Down/Fatal
5Local/Communication Error
6Exclusive Local Error
7Cashout Error
8In Supervisory
9Closed
10Encryptor Error
11Reject Bin Error
12All Cassettes Down/Fatal Admin Cash
13Cash Acceptor Faults
14AB Full/Reject bin Overfill
sumtotal


If so, I know how to do it. No screenshots are required. Please provide the name of your new sheet
 

buvanamali

New Member
Joined
Jul 27, 2014
Messages
26
Sorry, I can't open that link. I just need a screenshot.

Due to my limited English level, I may have misunderstood your intention. I think you want to create a new sheet, sum count of each errors (from all sheets)

Is that so?
test.xlsm
AB
1errtotal
2Card Reader Errorfrom all sheets
3Cash Handler Error
4All Cassettes Down/Fatal
5Local/Communication Error
6Exclusive Local Error
7Cashout Error
8In Supervisory
9Closed
10Encryptor Error
11Reject Bin Error
12All Cassettes Down/Fatal Admin Cash
13Cash Acceptor Faults
14AB Full/Reject bin Overfill
sumtotal


If so, I know how to do it. No screenshots are required. Please provide the name of your new sheet
Ok. That's what I wanted to do so. I append the below the sheet names. This
Error List (Error Count will not be there). So no concentration is required from here.
Master (It starts from here) - This is only an example. Sheet names may vary. Similarly no of sheets may also vary. Please Provide me a solution.
AA
AB
AC
BA
BB
BC
Bd
******Also one important thing****** --- I would like to mention that all columns except the following columns are ok. The following columns G, H, O, P, Q columns are having "/" symbol, in between the errors. Hence the problem, it is not reflecting the correct no of errors. It shows "0".
Please the same also to be taken into consideration while giving final touch to the resolution.

Thanks in Advance.

Buvanamali
 

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
929
Office Version
  1. 2013
Platform
  1. Windows
Tell me what about
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,269
Office Version
  1. 365
Platform
  1. Windows
@mohadin
Have you posted this to the wrong thread?
 

Watch MrExcel Video

Forum statistics

Threads
1,123,263
Messages
5,600,597
Members
414,392
Latest member
Josephkaipa

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