Autofilter table with formatted currency

[winsbury]

This sounds super easy, but after 3 days of trying everything I can think of its time to come clean and admit I need help....

In Table 1 I have a column of transactions formatted as
_(£* #,##0.00_);_(£* (#,##0.00);_(£* "-"??_);_(@_)

I wish to filter on an exact value, for example £500.00

When doing this manually in the table header by ticking the box next to the value £500.00 the filter works just fine and returns only the records where the value is exactly £500.00

However, recording the exact same actions into a macro, returns code below which always returns zero records:

ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=1, Criteria1:="£500.00"

How do I get it to return records with the stated value ?

 

[winsbury]

The cells contain numbers, it's only the formatting which is currency. Surprisingly >,<,<>,>=,<= all work as expected so it is possible to construct a nasty looking line which filters on an exact value but there has to be a better way to do a simple equals (=) than this monstrosity, also since in the actual code the criteria is held in variable from user input form textbox this method prevents the user from using >,< etc in their criteria.

ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=1, Criteria1:="500"     'returns zero records
ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=1, Criteria1:=">=500", Criteria2:="<=500"     'correctly returns records with an exact value of £500.00