Excel VBA filtering

  • Thread starter Costas Limassol
  • Start date
C

Costas Limassol

I am working on a database of numbers and I want to filter only the zero
values of a particular field. I tried the following code but no records were
found:
Selection.AutoFilter Field:=8, Criteria1:="0,00"
I even tried recording my moves for a number of different critieria and I
made sure that my criteria resulted in some records been found. However when
I tried to run my moves no records were found. Below are the moves I recorded:
ActiveSheet.Range("$A$1:$J$258").AutoFilter Field:=8, Criteria1:= _
"(5.115,00)"
ActiveSheet.Range("$A$1:$J$258").AutoFilter Field:=8, Criteria1:= _
"=(3.096,00)", Operator:=xlOr, Criteria2:="=(3.000,00)"
ActiveSheet.Range("$A$1:$J$258").AutoFilter Field:=8, Criteria1:=Array( _
"(1.117,99)", "(1.270,00)", "(1.414,00)", "(1.914,00)", "(1.960,00)",
"(2.451,00)", _
"(3.000,00)", "(3.096,00)", "(335,00)", "(487,80)", "(5.115,00)",
"(550,00)", _
"(652,00)", "(700,00)", "(72,00)", "(74,00)", "(74,05)", "400,00"),
Operator:= _
xlFilterValues
ActiveSheet.Range("$A$1:$J$258").AutoFilter Field:=8, Criteria1:="0,00"
I even restarted excel but no luck. Can anyone help?
 
M

Mike Fogleman

Remember that VBA only reads English and it does not recognize 0,00 as a
number, it sees it as text. In your worksheet you have local settings that
make the comma appear as a decimal point would in an English worksheet. That
is OK for the worksheet to use the comma decimal point and it knows how to
deal with numbers like that, but VBA does not. So you have numbers in your
worksheet and VBA is looking for a string to match to them. Like matching
apples to oranges, there is none.
Change your Criteria to "0.00" and VBA will now look for a number to match.

Mike F
 
C

Costas Limassol

It worked! Thanks a lot Mike!

Mike Fogleman said:
Remember that VBA only reads English and it does not recognize 0,00 as a
number, it sees it as text. In your worksheet you have local settings that
make the comma appear as a decimal point would in an English worksheet. That
is OK for the worksheet to use the comma decimal point and it knows how to
deal with numbers like that, but VBA does not. So you have numbers in your
worksheet and VBA is looking for a string to match to them. Like matching
apples to oranges, there is none.
Change your Criteria to "0.00" and VBA will now look for a number to match.

Mike F
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top