Excel VBA filtering

  • Thread starter Thread starter Costas Limassol
  • Start date 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?
 
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
 
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
Click to expand...
 
Back
Top