Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA filtering
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA filtering
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 "Costas Limassol" wrote in message ... 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA filtering
It worked! Thanks a lot Mike!
"Mike Fogleman" wrote: 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 "Costas Limassol" wrote in message ... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel filtering | Excel Worksheet Functions | |||
Filtering in Excel | Excel Discussion (Misc queries) | |||
EXCEL FILTERING | Excel Discussion (Misc queries) | |||
Excel Filtering | Excel Discussion (Misc queries) | |||
trouble filtering a list. Why isn't column filtering? | Excel Worksheet Functions |