Search function looking for value in multiple columns and filtering

[sofiachr]

Hi,

I have this Excel sheet where I have my training exercises and I want to aply a search function since they are becoming many now. I have the muscle the exersice activates in column A and B and C since there are normaly multiple muscels activated. (In Swedish at picture, Muskel 1 and 2 and 3).

The other criterias, Kondition (condition) and Namn (name), works. But the muscel criteria (Muskel) need to be looking for the search value in both column A and B and C (from row 6 and down) since it could be in any of these columns.

I would like to create a macro I can link to the search button (Sök) to simplify daily use. And the end result should be a filtered list with those exercises that are activating the muscle I searched for.

Thanks!
Sincerely, Sofia

 

[juddaaaa]

This should work for you

Sub SearchMuscles()

    Dim fnd As Range, last_row As Long, current_row As Range
    Dim muscle As String
    
    With Sheets("Sheet1") '<--CHANGE "Sheet1" TO THE NAME OF YOUR WORKSHEET
        .Rows.EntireRow.Hidden = False
        
        muscle = .Range("A3").Value
        last_row = .Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        
        For Each current_row In .Rows("6:" & last_row)
            With current_row
                Set fnd = Range(.Cells(, "A"), .Cells(, "C")).Find( _
                    What:=muscle, _
                    LookIn:=xlValues, _
                    SearchOrder:=xlByColumns)
                    
                If fnd Is Nothing Then
                    .EntireRow.Hidden = True
                End If
            End With
        Next current_row
    End With

End Sub

 

[sofiachr]

This should work for you

Sub SearchMuscles()

    Dim fnd As Range, last_row As Long, current_row As Range
    Dim muscle As String
   
    With Sheets("Sheet1") '<--CHANGE "Sheet1" TO THE NAME OF YOUR WORKSHEET
        .Rows.EntireRow.Hidden = False
       
        muscle = .Range("A3").Value
        last_row = .Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
       
        For Each current_row In .Rows("6:" & last_row)
            With current_row
                Set fnd = Range(.Cells(, "A"), .Cells(, "C")).Find( _
                    What:=muscle, _
                    LookIn:=xlValues, _
                    SearchOrder:=xlByColumns)
                   
                If fnd Is Nothing Then
                    .EntireRow.Hidden = True
                End If
            End With
        Next current_row
    End With

End Sub

Thanks! It works perfect. How do I make it work with the other two fields? The kondition (condition) and Namn (name). I could use a simple advanced filter for these before but it would be good to make these two solutions work in one click. So that these are considered when I press search (Sök) too.

 

[DanteAmor]

How do I make it work with the other two fields?

With advanced filter , but you must have more rows, check the following example.
The names of the headers in A2 through E2 must be equal to the names of the headers in A1 through E10

varios 08feb2020.xlsm
	A	B	C	D	E	F
1
2	Muskel 1	Muskel 2	Muskel 3	Kondition?	Namn
3	Rumpa			nej
4		Rumpa		nej
5			Rumpa	nej
6
7
8
9
10	Muskel 1	Muskel 2	Muskel 3	Kondition?	Namn	Besk
11	Mage			Nej	a
12	Mage			Nej	b
13		Rumpa		Nej	c
14			Rumpa	Nej	d
15	Rumpa			Nej	e
16		Rumpa		Ja	f
17			Rumpa	Ja	g
18	Rumpa				h
sheet

The macro:

Sub Macro3()
  Range("A10:F" &  Range("E" & Rows.Count).End(xlUp).Row).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("A2:E5")
End Sub

 

[juddaaaa]

Just a small adjustment

Sub SearchMuscles()

    Dim mySheet As Worksheet
    Dim fnd As Range, last_row As Long, current_row As Range
    Dim muscle As String
  
    Set mySheet = Sheets("Sheet1") '<--CHANGE "Sheet1" TO THE NAME OF YOUR WORKSHEET
  
    With mySheet
        .Rows.EntireRow.Hidden = False
      
        muscle = .Range("A3").Value
        last_row = .Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
      
        For Each current_row In .Rows("6:" & last_row)
            With current_row
                Set fnd = Range(.Cells(, "A"), .Cells(, "C")).Find( _
                    What:=muscle, _
                    LookIn:=xlValues, _
                    SearchOrder:=xlByColumns)
                  
                If fnd Is Nothing Then
                    .EntireRow.Hidden = True
                Else
                    If mySheet.Range("B3") <> Empty Then
                        If .Cells(, "D") <> mySheet.Range("B3") Then
                            .EntireRow.Hidden = True
                        End If
                    End If
                   
                    If mySheet.Range("C3") <> Empty Then
                        If .Cells(, "E") <> mySheet.Range("C3") Then
                            .EntireRow.Hidden = True
                        End If
                    End If
                End If
            End With
        Next current_row
    End With

End Sub

 

[sofiachr]

Thanks! Now everything is working.