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
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

hnsd24_CN

Board Regular
Joined
Oct 13, 2020
Messages
78
Office Version
  1. 2016
Platform
  1. Windows
Add a new sheet (named total), the result will be put on range (B2: B14)
VBA Code:
Sub total_err()
    Dim ws As Worksheet
    Dim rng As Range
    Dim r As Integer
    Dim ws_total As Worksheet
    Dim i As Integer
    Dim arr(1 To 13) As Long
    Set ws_total = Worksheets("total")
    For Each ws In Worksheets
        If ws.Name <> "total" Then
            i = 1
            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), " ", ""))
                arr(i) = arr(i) + ws.Cells(r, rng.Column)
                i = i + 1
            Next
        End If
    Next
    ws_total.Range("B2:B14") = Application.Transpose(arr)
End Sub
I still have some doubts

1. You said that the name of the new worksheet is uncertain. Do you need to put sum total of each errors (from all sheets) on many worksheets?

2. I checked the code again. "/" will not have any impact on the calculation results. I don't know where the problem is. Maybe you need to take a screenshot of Problematic sheet, and if there is a formula in the table, also show it(Only one representative is needed)
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,702
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
My code in Post #2 does create a FULL total of all errors IF you follow the instructions provided !!
 

buvanamali

New Member
Joined
Jul 27, 2014
Messages
26
Add a new sheet (named total), the result will be put on range (B2: B14)
VBA Code:
Sub total_err()
    Dim ws As Worksheet
    Dim rng As Range
    Dim r As Integer
    Dim ws_total As Worksheet
    Dim i As Integer
    Dim arr(1 To 13) As Long
    Set ws_total = Worksheets("total")
    For Each ws In Worksheets
        If ws.Name <> "total" Then
            i = 1
            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), " ", ""))
                arr(i) = arr(i) + ws.Cells(r, rng.Column)
                i = i + 1
            Next
        End If
    Next
    ws_total.Range("B2:B14") = Application.Transpose(arr)
End Sub
I still have some doubts

1. You said that the name of the new worksheet is uncertain. Do you need to put sum total of each errors (from all sheets) on many worksheets?

2. I checked the code again. "/" will not have any impact on the calculation results. I don't know where the problem is. Maybe you need to take a screenshot of Problematic sheet, and if there is a formula in the table, also show it(Only one representative is needed)

My code in Post #2 does create a FULL total of all errors IF you follow the instructions provided !!
Dear Mr Michael M
Sorry for Delayed Response. Your code doesn't give expected results. I am enclosing the screenshot for your perusal. Whereas Mr [B]hnsd24_CN[/B]'s post 13 working well. Thank you for your response.[ATTACH type="full"]24942[/ATTACH][B][/B]





My code in Post #2 does create a FULL total of all errors IF you follow the instructions provided !!
 

Attachments

  • TestFile1.png
    TestFile1.png
    20.2 KB · Views: 7

buvanamali

New Member
Joined
Jul 27, 2014
Messages
26

ADVERTISEMENT

Tell me what about

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
Dear Mr Mohadin
Sorry for delayed response. Your code also gives desired result but for only active sheet. I need it for multiple sheet. Your favourable response is solicited.
buvanamali
 

buvanamali

New Member
Joined
Jul 27, 2014
Messages
26
Add a new sheet (named total), the result will be put on range (B2: B14)
VBA Code:
Sub total_err()
    Dim ws As Worksheet
    Dim rng As Range
    Dim r As Integer
    Dim ws_total As Worksheet
    Dim i As Integer
    Dim arr(1 To 13) As Long
    Set ws_total = Worksheets("total")
    For Each ws In Worksheets
        If ws.Name <> "total" Then
            i = 1
            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), " ", ""))
                arr(i) = arr(i) + ws.Cells(r, rng.Column)
                i = i + 1
            Next
        End If
    Next
    ws_total.Range("B2:B14") = Application.Transpose(arr)
End Sub
I still have some doubts

1. You said that the name of the new worksheet is uncertain. Do you need to put sum total of each errors (from all sheets) on many worksheets?

2. I checked the code again. "/" will not have any impact on the calculation results. I don't know where the problem is. Maybe you need to take a screenshot of Problematic sheet, and if there is a formula in the table, also show it(Only one representative is needed)
Dear Mr hnsd24_CN

I tested your code. It didn't give desired result. My requirement is as per details below:

Sheet Name E1 to Q1 Header
Master Count of Errors for each Header from E1 to Q1 (ie. last row generated with your Post 13.) Each row for each sheet.


With regard to no.2, we shall ignore that.

Advance Thanks
 

Michael M

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

ADVERTISEMENT

Dear Mr Michael M
Sorry for Delayed Response. Your code doesn't give expected results. I am enclosing the screenshot for your perusal. Whereas Mr [B]hnsd24_CN[/B]'s post 13 working well. Thank you for your response.[ATTACH type="full"]24942[/ATTACH][B][/B]





The reason you get an incorrect result with my code is that the Text in the Error List sheet is DIFFERENT to the text in the columns on each sheet.
The "Error List" text has spaces in it .....the text in the other sheets does not !!
 

buvanamali

New Member
Joined
Jul 27, 2014
Messages
26
The reason you get an incorrect result with my code is that the Text in the Error List sheet is DIFFERENT to the text in the columns on each sheet.
The "Error List" text has spaces in it .....the text in the other sheets does not !!
Dear Mr Michael M
Ok I will again test it and revert to you.

Thank you for kindness.
 

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
885
Office Version
  1. 2013
Platform
  1. Windows
Dear Mr Mohadin
Sorry for delayed response. Your code also gives desired result but for only active sheet. I need it for multiple sheet. Your favourable response is solicited.
buvanamali
Well
I will carry on for other sheets
and come back to to you
 

hnsd24_CN

Board Regular
Joined
Oct 13, 2020
Messages
78
Office Version
  1. 2016
Platform
  1. Windows
Add a new sheet (named total), the result will be put on range (B2: B14)
VBA Code:
Sub total_err()
    Dim ws As Worksheet
    Dim rng As Range
    Dim r As Integer
    Dim ws_total As Worksheet
    Dim i As Integer
    Dim arr(1 To 13) As Long
    Set ws_total = Worksheets("total")
    For Each ws In Worksheets
        If ws.Name <> "total" Then
            i = 1
            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), " ", ""))
                arr(i) = arr(i) + ws.Cells(r, rng.Column)
                i = i + 1
            Next
        End If
    Next
    ws_total.Range("B2:B14") = Application.Transpose(arr)
End Sub
I still have some doubts

1. You said that the name of the new worksheet is uncertain. Do you need to put sum total of each errors (from all sheets) on many worksheets?

2. I checked the code again. "/" will not have any impact on the calculation results. I don't know where the problem is. Maybe you need to take a screenshot of Problematic sheet, and if there is a formula in the table, also show it(Only one representative is needed)
There may be something wrong with our communication

This code contains two steps

Step1: calculate the count of each error on each worksheet and put the result on the last row of each sheet.The code for this step has been verified by you and it is feasible.
Step2:On a new worksheet(I named it total), total the count of each error from all worksheet(toat the results of step 1)
Which step didn't get the desired result?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,448
Messages
5,596,210
Members
414,046
Latest member
mbeutler1203

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