1

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.

SamwiseVB
  • 51
  • 8
  • 1
    I'll guess that the data on your `Data` sheet is probably using text that looks like numbers instead of being actual numbers. – braX Feb 02 '23 at 07:10
  • You firstly must determine what is the data format of the "Data" sheet, as correctly was suggested in the above comment. If so, you should firstly autofit the column "C:C" and use `.range("C6").Text`. – FaneDuru Feb 02 '23 at 07:42
  • If you want to avoid formatting it as the answers illustrate, you can address why the `Data` is not numeric in the first place. Maybe you imported that `Data` using something that did not treat them as numeric values. If you can first change how that data is populated instead, that would probably be a better solution than getting the formatting to match, as that could change. – braX Feb 02 '23 at 08:54
  • The data was imported from an Access DB. Wouldn't that set the data type? I also have the cells formatted as Number with 4 decimals, I tried a custom as well "#0.0000#" – SamwiseVB Feb 02 '23 at 15:16

2 Answers2

2

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

Jos Woolley
  • 8,564
  • 2
  • 4
  • 9
0

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
andrewb
  • 1,129
  • 5
  • 9
  • 1
    This can cause problems if the column is not wide enough, as it could return something like `###` instead of the desired value. – braX Feb 02 '23 at 07:27
  • @braX hmm you're right, I just tested this. Well, I'll just leave this here anyway. Maybe Jos Woolley's answer is a better solution but that one would cease to work if OP decides to change the formatting of the data at any point. Although that is probably less of a concern. – andrewb Feb 02 '23 at 07:38
  • @braX I thought of another solution, can you take a look? This should work right? – andrewb Feb 02 '23 at 07:54