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]

Please reply to the results of the code run and attach a screenshot

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

 

[buvanamali]

Please reply to the results of the code run and attach a screenshot

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

 

[hnsd24_CN]

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]

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]

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]

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

Test file.xls

Shared with Dropbox

www.dropbox.com

Login - Dropbox

 

[hnsd24_CN]

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

Test file.xls

Shared with Dropbox

www.dropbox.com

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
	A	B
1	err	total
2	Card Reader Error	from all sheets
3	Cash Handler Error
4	All Cassettes Down/Fatal
5	Local/Communication Error
6	Exclusive Local Error
7	Cashout Error
8	In Supervisory
9	Closed
10	Encryptor Error
11	Reject Bin Error
12	All Cassettes Down/Fatal Admin Cash
13	Cash Acceptor Faults
14	AB 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]

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
	A	B
1	err	total
2	Card Reader Error	from all sheets
3	Cash Handler Error
4	All Cassettes Down/Fatal
5	Local/Communication Error
6	Exclusive Local Error
7	Cashout Error
8	In Supervisory
9	Closed
10	Encryptor Error
11	Reject Bin Error
12	All Cassettes Down/Fatal Admin Cash
13	Cash Acceptor Faults
14	AB 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]

Tell me what about

Dropbox - File Deleted - Simplify your life

www.dropbox.com

 

[Fluff]

@mohadin
Have you posted this to the wrong thread?