VBA code needed to search and find 2 values in a spreadsheet

[Anell JvR]

I have a spreadsheet with a long list of countries of the world on the left.

The columns are then made up of different sickness types. Users must access the list to find the country and then, depending on the sickness type, find the rule that apply to that specific illness in that specific country.

They therefore have to scroll up and down a lot and it takes time to make sure they are viewing the correct rule in the correct row for that country.

The requirement is a search type function that will take the user to the cell where the country and the specific sickness type "meet".

I created a dropdown list for the countries and the sickness types by using Data Validation, and I used macros to get to the country successfully, move it up and to change the background colour of the cell so the country's name can stand out.

I however need to go to the cell applicable to the specific sickness type to find the rule for that country. Any suggestions will be welcome!

 

[Roger Govier]

Hi

I would use Data > Advanced Filter.
Insert a few rows above your source data.
Type Country in A1 and Sickness in B1
Enter required Country in A2 and sickness type in B2 and run the code

Sub FilterData()

    Range("A5:G26").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
        Range("B1:C2"), Unique:=False

End Sub

Obviously change the range to match your range.
Either attach the macro to a button on the sheet, or place some event code behind the sheet so that if the value in A2 or B2 changes, the macro gets called.

 

[Anell JvR]

Hi

I would use Data > Advanced Filter.
Insert a few rows above your source data.
Type Country in A1 and Sickness in B1
Enter required Country in A2 and sickness type in B2 and run the code

Sub FilterData()

    Range("A5:G26").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
        Range("B1:C2"), Unique:=False

End Sub

Obviously change the range to match your range.
Either attach the macro to a button on the sheet, or place some event code behind the sheet so that if the value in A2 or B2 changes, the macro gets called.

Thank you very much Roger, much appreciated! For some reason I get an "Out of Memory" error but I'll review my code. I have not worked in VBA for a couple of years now and became so rusty!