Help Need with Macro to filter based on a value & to return values

Hi Friends,

Need help in Excel, I really don't know how to solve this problem.

I have attached a sample worksheet. Input is in Sheet1 & Output expected in Sheet2

Conditions/Steps:
1. Filter with column B (Pass)
2. with the filtered list(Only pass list), check in column C(Comments),
& Output the following into a new sheet.
In the comments,
List the number of rows that contains 90 in column 90 & above
List the number of rews that contains 95 in column 90 above and so on"

Any help is really appreciated.. Manually doing this work is really hard for me. it takes a lot of time..

Maybe:

Please Login or Register  to view this content.

Try this:

Sub rr()
Dim iVal, iVal1, iVal2, iVal3 As Integer
iVal = Application.WorksheetFunction.CountIf(Range("C1:C10000"), "*70")
iVal1 = Application.WorksheetFunction.CountIf(Range("C1:C10000"), "*80")
iVal2 = Application.WorksheetFunction.CountIf(Range("C1:C10000"), "*90")
iVal3 = Application.WorksheetFunction.CountIf(Range("C1:C10000"), "*95")
Range("D1") = "70 & above" 'output in Range D
Range("D2") = "80 & above" 'output in Range D
Range("D3") = "90 & above" 'output in Range D
Range("D4") = "95 & above" 'output in Range D
Range("E1") = iVal 'output in Range E
Range("E2") = iVal1 'output in Range E
Range("E3") = iVal2 'output in Range E
Range("E4") = iVal3 'output in Range E


' set the range to display the output

Cells.AutoFilter

End Sub

Hi John,

Thank you very much for your help.. It works exactly how i wanted.

One more thing, instead of counting number, if i need to count for a wildcard text like "ansdhkha", what should i do?

Many Thanks

Originally Posted by JOHN H. DAVIS

Maybe:

Please Login or Register  to view this content.

Hello ExcelHelpSeeker,

Thank you very much for your response.

But, initially , i need the excel to be filtered based on "Pass" condition in column B.

Can u pls look into it?

Thanks again.

Originally Posted by excelhelpseeker

Try this:

Sub rr()
Dim iVal, iVal1, iVal2, iVal3 As Integer
iVal = Application.WorksheetFunction.CountIf(Range("C1:C10000"), "*70")
iVal1 = Application.WorksheetFunction.CountIf(Range("C1:C10000"), "*80")
iVal2 = Application.WorksheetFunction.CountIf(Range("C1:C10000"), "*90")
iVal3 = Application.WorksheetFunction.CountIf(Range("C1:C10000"), "*95")
Range("D1") = "70 & above" 'output in Range D
Range("D2") = "80 & above" 'output in Range D
Range("D3") = "90 & above" 'output in Range D
Range("D4") = "95 & above" 'output in Range D
Range("E1") = iVal 'output in Range E
Range("E2") = iVal1 'output in Range E
Range("E3") = iVal2 'output in Range E
Range("E4") = iVal3 'output in Range E


' set the range to display the output

Cells.AutoFilter

End Sub

Maybe use if and like together?

Please Login or Register  to view this content.

Thanks Again John.. You saved my day...!!!

Originally Posted by JOHN H. DAVIS

Maybe use if and like together?

Please Login or Register  to view this content.

You're welcome. Glad to help out.