Excel autofilter - doesn't find trailing 0s

Question

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 :)

Edit: This happens for a data lookup as well (for loop).

for i = 1 to LastRow
    If Not IsError(Worksheets("Data").Cells(i, 1).Value) Then
            If strItem = Worksheets("Data").Cells(i, 1).Value Then
                intRow = Trim(fncRemoveAlpha(Replace(Worksheets("Data").Cells(i, 1).Address, "$", "")))
                
                Me.Cells(12, 6).Value = Worksheets("Data").Cells(intRow, 14)
            end if
   end if
next i

I understand it is a text comparison and not a number comparison, but I'm not sure why it isn't a number comparison to begin with. Formatting the cell and/or the data does not seem to resolve this.

Answer 1

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")

Answer 2

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