Hi all!
I'm trying to use the autofilter function by referencing a cell value in another Worksheet. To be more detailed:
- I am performing the autofilter on a Sheet called "Product Name"
- I have a cell value in a Sheet called "Base" (H3) that i want to look for in my Autofilter. The Autofilter should not look for the specific value, but should filter for everything CONTAINING this value.
(- the end goal is to perform this on several values (H4, H5, H6...) and if an empty cell is detected (H7 is empty for example) the autofilter does not filter for this blank value, instead the blank value is recognized and the macro is completed....but I would be super happy to receive an answer on the first part already, this "final" part would just be an icing on the cake
)
I have looked i think at every single post regarding this issue in the last two days but all the solutions provided did not work for me. I don't even remember everything I tried, but here are at least two things that did not work for me:
______________________________________________________________________________________________________________________________________________
"Solution"1
Dim rng As Range
Dim strName As String
strName = Sheet1.Cells(3, 8) 'checking this value gave me the correct value of Worksheets("Product Name") Cell H3 // Sheet1 is the worksheet called "Product Name"
'Defining range, which will be filtered
Set rng = Worksheets("Product Name").Cells
rng.AutoFilter Field:=40, Criteria1:="*" & strName & "*", Operator:=xlFilterValues
______________________________________________________________________________________________________________________________________________
"Solution"2
another solution i found was with RANGE
Dim rng As Range
'Defining range, which will be filtered
Set rng = Worksheets("Product Name").Cells
rng.AutoFilter Field:=40, Criteria1:=Sheet1."*" & Range("H3").Value & "*", Operator:=xlFilterValues
_______________________________________________________________________________________________________________________________________________
I thought this should be easy as i saw everywhere people using *, but it does not seem to work me ...
Your help would be HUGELY appreciated!!
ah and my error message is always: Runtime Error 1004: Select Method of Range class failed
after googling the error message i tried activating the appropriate sheet, but it did not help...
I'm trying to use the autofilter function by referencing a cell value in another Worksheet. To be more detailed:
- I am performing the autofilter on a Sheet called "Product Name"
- I have a cell value in a Sheet called "Base" (H3) that i want to look for in my Autofilter. The Autofilter should not look for the specific value, but should filter for everything CONTAINING this value.
(- the end goal is to perform this on several values (H4, H5, H6...) and if an empty cell is detected (H7 is empty for example) the autofilter does not filter for this blank value, instead the blank value is recognized and the macro is completed....but I would be super happy to receive an answer on the first part already, this "final" part would just be an icing on the cake
)I have looked i think at every single post regarding this issue in the last two days but all the solutions provided did not work for me. I don't even remember everything I tried, but here are at least two things that did not work for me:
______________________________________________________________________________________________________________________________________________
"Solution"1
Dim rng As Range
Dim strName As String
strName = Sheet1.Cells(3, 8) 'checking this value gave me the correct value of Worksheets("Product Name") Cell H3 // Sheet1 is the worksheet called "Product Name"
'Defining range, which will be filtered
Set rng = Worksheets("Product Name").Cells
rng.AutoFilter Field:=40, Criteria1:="*" & strName & "*", Operator:=xlFilterValues
______________________________________________________________________________________________________________________________________________
"Solution"2
another solution i found was with RANGE
Dim rng As Range
'Defining range, which will be filtered
Set rng = Worksheets("Product Name").Cells
rng.AutoFilter Field:=40, Criteria1:=Sheet1."*" & Range("H3").Value & "*", Operator:=xlFilterValues
_______________________________________________________________________________________________________________________________________________
I thought this should be easy as i saw everywhere people using *, but it does not seem to work me ...
Your help would be HUGELY appreciated!!
ah and my error message is always: Runtime Error 1004: Select Method of Range class failed
after googling the error message i tried activating the appropriate sheet, but it did not help...
