AutoFilter

[Guest]

I am having trouble getting AutoFilter to work using VB code. The problem is
that the Criteria sometimes changes depending upon the format of the data.
For example using the data below, the code:
Selection.AutoFilter Field:=2, Criteria1:="-49276.80078"
sometimes filters the data. But if the number format is #.00 then the code:
Selection.AutoFilter Field:=2, Criteria1:="-49276.80" is required.
This is further complicated by the fact that the code:
Selection.AutoFilter Field:=2, Criteria1:="-50000"
is required reguardless of Number format. That is
Selection.AutoFilter Field:=2, Criteria1:="-50000.0"
WILL NOT WORK.

Col A Col B
1 -50000
2 -49276.80078
3 -48476.80078
4 -47676.80078
5 -46876.80078

Is this a bug?

Tx,

Randall

 

[Guest]

Hi
You may need to format numbers in column to what is in your macro or use
something like
For Each CELL In Range("B:B")
MYV = CELL
Selection.AutoFilter Field:=2, Criteria1:=MYV
Next CELL
so criteria to select is always whats on your sheet in correct format

Tina