Count if macro on multiple criteria
Solved/Closed
Related:
- Count if macro on multiple criteria
- Isnumber(search multiple criteria) ✓ - Forum - Excel
- Excel data validation custom multiple criteria - Guide
- Add when multiple criteria is met in rows ✓ - Forum - Excel
- Multiple criteria sums ✓ - Forum - Office Software
- Conditional formatting on multiple criteria ✓ - Forum - Excel
2 replies
venkat1926
Feb 13, 2011 at 04:20 AM
- Posts
- 1864
- Registration date
- Sunday June 14, 2009
- Status
- Contributor
- Last seen
- August 7, 2021
Feb 13, 2011 at 04:20 AM
try this macro "test". the macro undo is to undo the result of the macro test
Sub test()
Dim r As Range, j As Integer, k As Integer, m As Integer
Worksheets("sheet2").Activate
j = Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Row
k = j
Do
If k = 1 Then Exit Do
If Cells(k, "C").Offset(-2, 0) = "" Then
Set r = Cells(k, "c").Offset(-1, 0)
Else
Set r = Range(Cells(k, "C").Offset(-1, 0), Cells(k, "c").Offset(-1, 0).End(xlUp))
End If
'msgbox r.Address
m = WorksheetFunction.CountIf(r, "false")
'msgbox m
Cells(k, "c") = m
If Cells(k, "c").End(xlUp).Address = "$C$1" Then Exit Sub
k = Cells(k - 1, "c").End(xlUp).Offset(-1, 0).Row
'msgbox k
Loop
End Sub
Sub undo()
Dim r As Range, c As Range
Worksheets("sheet2").Activate
Set r = Range(Range("C1"), Cells(Rows.Count, "C").End(xlUp))
For Each c In r
If WorksheetFunction.IsNumber(c) Then c.Clear
Next c
End Sub
venkat1926
Feb 17, 2011 at 09:33 PM
- Posts
- 1864
- Registration date
- Sunday June 14, 2009
- Status
- Contributor
- Last seen
- August 7, 2021
Feb 17, 2011 at 09:33 PM
which is your operative sheet. I have taken as sheet2. if it is some other sheet then correct the statement
Worksheets("sheet2").Activate
in BOTH the macros.
I have given again the macro with some explantions and msgbox(s). you will get a pop up box giving you the results. if cick ok the macro will resume. this will give you an idea what the macro is doing. Afer you are convinced you can convert these msgbox(s) into comments by typing single spostrophe(') at the beginning of the statements.
As you have said "i want the macro " you know how and where to park the macros and how to un them.
Worksheets("sheet2").Activate
in BOTH the macros.
I have given again the macro with some explantions and msgbox(s). you will get a pop up box giving you the results. if cick ok the macro will resume. this will give you an idea what the macro is doing. Afer you are convinced you can convert these msgbox(s) into comments by typing single spostrophe(') at the beginning of the statements.
As you have said "i want the macro " you know how and where to park the macros and how to un them.
Sub test()
Dim r As Range, j As Integer, k As Integer, m As Integer
Worksheets("sheet2").Activate
j = Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Row
'j is the last item in the column C
MsgBox j
k = j
'loops through the cells in c starting from the last cell upto cell C2
Do
If k = 1 Then Exit Do
If Cells(k, "C").Offset(-2, 0) = "" Then
mgbox r.Address
Set r = Cells(k, "c").Offset(-1, 0)
Else
Set r = Range(Cells(k, "C").Offset(-1, 0), Cells(k, "c").Offset(-1, 0).End(xlUp))
End If
'msgbox r.Address
m = WorksheetFunction.CountIf(r, "false")
MsgBox m
Cells(k, "c") = m
If Cells(k, "c").End(xlUp).Address = "$C$1" Then Exit Sub
k = Cells(k - 1, "c").End(xlUp).Offset(-1, 0).Row
MsgBox k
Loop
End Sub
Feb 17, 2011 at 01:25 PM
Thank you very much for your help !!
I am not very much familiar about the above codes !!
Could you please help me out with an explaination !!
Again thank you very much !!