Excel autofilter - doesn't find trailing 0s-CodePudding

I have 2 sheets, one the user inputs data used as search criteria and the second sheet is the data being searched. Both my search value cell and column are formatted as a number with 4 decimal places: 0.0000

the user enters value into C6 want to filter search sheet, column 19 or S

Worksheets("Data").Range("$A$1:$AX$20000").autofilter Field:=19, criteria1:=Worksheets("Lookup").Range("C6")

Example: C6 on lookup sheet = 0.0650 but it sets the autofilter to 0.065 which returns nothing, which for some reason does not = 0.650 which returns appropriate results.

I'm very confused here and not sure how to look this up. Thanks in advance :)

CodePudding user response：

As pointed out here:

"When applying a filter for a single number we need to use the number formatting that is applied in the column."

So you should amend your code to:

Criteria1:=Format(Worksheets("Lookup").Range("C6"), "0.0000")

CodePudding user response：

You can try this:

Worksheets("Data").Range("$A$1:$AX$20000").autofilter Field:=19, criteria1:=Worksheets("Lookup").Range("C6").Text

The only change is the .Text at the end.

edit: Alright so I devised another solution, one that should work 100% of the time, no matter what formatting you use for either the search value or the data:

Dim searchValue As String, searchRange As Range
Set searchRange = Worksheets("Data").Range("$A$1:$AX$20000")
searchValue = Format(Worksheets("Lookup").Range("C6"), searchRange.Cells(2, 1).NumberFormat)
searchRange.AutoFilter Field:=19, Criteria1:=searchValue