Select Cells Case Statement

[chatnet26]

Dear Gentlemen,

I wanted to come up a result of using a select cells case statement. When after select cells of criteria, the field headers will filter so on and forth, as there were 29 table headers out of 12 data sheets maintaining in daily basis.

I can do this task using turn on filtering cells but keeping scrolling over to the right and left is trickier.

Will it be possible to do this using a macro?

Case select “Type”
Case Project
Activesheet.Range().autofilter

Case MOC
Activesheet.Range().Autofilter
Else if no matching criteria found
Then
Message box, no records criteria found

In this project, I have four criteria to complete this action. Please see requirement below.

Type Status Dept Actionee
Project Closed Dept1 Discipline 2
Project Active Dept 1 Discipline 1
Project Overdue Dept 2 Discipline 1
Project Overdue Dept 2 Discipline 2
Project Overdue Dept 2 Discipline 2
Project Active Dept 2 Discipline 2
Project Active Dept 1 Discipline 1
Project Active Dept 1 Discipline 1
Project Closed Dept 1 Discipline 1
Project Closed Dept 2 Discipline 2
MOC Closed Dept 2 Discipline 1
MOC Active Dept 2 Discipline 2
MOC Active Dept 2 Discipline 1
MOC Active Dept 1 Discipline 1
MOC Overdue Dept 1 Discipline 2
MOC Overdue Dept 2 Discipline 2
MOC Overdue Dept 1 Discipline 2

Appreciate your kind support. Thanks

Best Regards,

 

[chatnet26]

Dear Guys,

Apologize of my data that I had been posted last Thursday as it was screwed up. Just now I tried to rearrange and re-post back but I got the same line.

Can anyone please tell me how to post with a table?

Best regards,

 

[royUK]

The best way would be to provide a workbook example using DropBox, OneDrive or similar

 

[chatnet26]

Dear Sir royUK,

Many thanks to your quick reply. I go now with your recommendation to create account at DropBox or OneDrive then I will get you back.

Best Regards,

 

[chatnet26]

Dear Sir royUK,

Okay now I have account at DropBox please send out your email so I can share you the shared folder.

Best regards,

 

[royUK]

Post the link here

 

[chatnet26]

Dear Sir royUK,

Here is the link below relevant to your request.

Link

Please let me know of any difficulty.

Best regards,

 

[chatnet26]

Dear Sir royUK,

Now after several times trying myself to figure out, I come up this in my mind. We can say like below

Select Case Range ("F1").Value
if Range("F1").Value = Project Then
Range("A2").AutoFilter ?????????????
Else If Range("F1").Value = Range("A2").Value = 0 then
MsgBox "No records matching"

End If

I am not sure what is the next step and how to apply this in the workbook. I admit myself I am a beginner of vba but keeping push to come up a resolution.

Best regards,

 

[royUK]

You don't need a Select case at all. Something like this filtering by the selection made in the cells,

  With Sheet1
  .Range("$A$1").CurrentRegion.AutoFilter Field:=1, Criteria1:=.Range("G1").Value
  .Range("$A$1").CurrentRegion.AutoFilter Field:=2, Criteria1:=.Range("G2").Value
   End With

 

[chatnet26]

Dear Sir royUK,

Many thanks to your quick response.

Now I apply your method and put into ThisWorkbook and put the code into the procedure of SheetChange. But when I click to cell G1 it gives me nothing while it filters from field1 and field2.

I think there is need to correct my application, right!

Best regards,