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

 

[mohadin]

@mohadin
Have you posted this to the wrong thread?

Yes ,
Sorry
And thank you
Corrected

 

[hnsd24_CN]

Add a new sheet (named total), the result will be put on range (B2: B14)

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]

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

 

[buvanamali]

Add a new sheet (named total), the result will be put on range (B2: B14)

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 !!

 

[buvanamali]

Tell me what about

Dropbox - File Deleted - Simplify your life

www.dropbox.com

What about

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]

Add a new sheet (named total), the result will be put on range (B2: B14)

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]

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]

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]

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]

Add a new sheet (named total), the result will be put on range (B2: B14)

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？