VBA table filter not working..

BenGee

Board Regular
Joined
Mar 5, 2016
Messages
164
Hi

This part of my code applies a greater than or equal to filter successfully;
Code:
        If ActiveSheet.Range("G3").Value <> "" Then
            .AutoFilter Field:=7, Criteria1:=">=" & ActiveSheet.Range("G3").Value
        End If
But I want it to be equal to, but when I change it, it doesn't filter. I've tried 2 variations;
Code:
        If ActiveSheet.Range("G3").Value <> "" Then
            .AutoFilter Field:=7, Criteria1:="=" & ActiveSheet.Range("G3").Value
        End If
And;
Code:
        If ActiveSheet.Range("G3").Value <> "" Then
            .AutoFilter Field:=7, Criteria1:=ActiveSheet.Range("G3").Value
        End If
The contents of G3 and field 7 are in currency format. If I change to text, the equals filter works but I need to keep as currency + I can't work out why it only works for >=

Thanks in advance for your help
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

GR00007

Board Regular
Joined
Apr 22, 2015
Messages
184
The values you are trying to match are formatted as currency - but that doesn't show what the actual value of the cell is. Excel will round to the decimals chosen to view and any value that doesn't match exactly to what you have in G3 will not "=". Please check both what the actual values of G3 and what you are matching to actually do match.
 

BenGee

Board Regular
Joined
Mar 5, 2016
Messages
164
Ok, thanks for your help.

All the value look the same. For example, the value of G3 is "1000" (showing as a currency of £1000), and, in my table there is a matching value of "1000" also showing as a currency of £1000. So still unsure why it's not recognising this when the code is "=" but can recognise these values when it's "<=".

Ive converted all to text and it's working however so will do.

Thanks for your help :)
 

Watch MrExcel Video

Forum statistics

Threads
1,101,820
Messages
5,483,095
Members
407,379
Latest member
Bender1964

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top