M
mark.driscol
Excel 2002 SP3, Windows 2002 SP1
I have seen postings regarding problems selecting values/setting
AutoFilter criteria in VBA due to number format problems. All of the
postings, however, involve problems with dates, which this posting does
not.
In one cell of a filtered range is the value -0.49038 formatted as
_(* #,##0_);_(* (#,##0);_(* "-"??_);_(@_)
which is one of the standard formats available from the Format Cells
dialog box. This value is displayed as "(0)", and I can select
"(0)" using a filter dropdown arrow.
However, I cannot do the same using VBA. If I use statements such as
ActiveSheet.AutoFilter.Range.AutoFilter _
Field:=1, _
Criteria1:=ActiveCell.Value
or
ActiveSheet.AutoFilter.Range.AutoFilter _
Field:=1, _
Criteria1:=Format(ActiveCell.Value,
ActiveCell.NumberFormat)
the correct filtered range is not displayed.
It seems to be a function of the Format statement and the fact that
"The Format function uses different format code strings than do the
NumberFormat and NumberFormatLocal properties" (from Excel VBA Help)
and rounding. For example, in the Immediate Window
?Format(ActiveCell.Value, "_(* #,##0_);_(* (#,##0);_(*
""-""??_);_(@_) ")
results in
_(-??_)
?Format(ActiveCell.Value, "#,##0_);(#,##0);""-""_)")
results in
-_)
However,
?Format(ActiveCell.Value, "#,##0.00_);(#,##0.00);""-""_)")
results in
(-.49)
Thus, it appears that somehow the actual value is being rounded to zero
by the formatting string "#,##0_);(#,##0);""-""_)", and as
such, the Immediate Window displays this value as zero.
Can anyone suggest how to get around this, or clarify the statement
from Excel Help, such that from VBA I can select values correctly? The
ability to select a value seems to depend on how it is formatted, not
strictly the value itself.
Thanks in advance.
Mark
I have seen postings regarding problems selecting values/setting
AutoFilter criteria in VBA due to number format problems. All of the
postings, however, involve problems with dates, which this posting does
not.
In one cell of a filtered range is the value -0.49038 formatted as
_(* #,##0_);_(* (#,##0);_(* "-"??_);_(@_)
which is one of the standard formats available from the Format Cells
dialog box. This value is displayed as "(0)", and I can select
"(0)" using a filter dropdown arrow.
However, I cannot do the same using VBA. If I use statements such as
ActiveSheet.AutoFilter.Range.AutoFilter _
Field:=1, _
Criteria1:=ActiveCell.Value
or
ActiveSheet.AutoFilter.Range.AutoFilter _
Field:=1, _
Criteria1:=Format(ActiveCell.Value,
ActiveCell.NumberFormat)
the correct filtered range is not displayed.
It seems to be a function of the Format statement and the fact that
"The Format function uses different format code strings than do the
NumberFormat and NumberFormatLocal properties" (from Excel VBA Help)
and rounding. For example, in the Immediate Window
?Format(ActiveCell.Value, "_(* #,##0_);_(* (#,##0);_(*
""-""??_);_(@_) ")
results in
_(-??_)
?Format(ActiveCell.Value, "#,##0_);(#,##0);""-""_)")
results in
-_)
However,
?Format(ActiveCell.Value, "#,##0.00_);(#,##0.00);""-""_)")
results in
(-.49)
Thus, it appears that somehow the actual value is being rounded to zero
by the formatting string "#,##0_);(#,##0);""-""_)", and as
such, the Immediate Window displays this value as zero.
Can anyone suggest how to get around this, or clarify the statement
from Excel Help, such that from VBA I can select values correctly? The
ability to select a value seems to depend on how it is formatted, not
strictly the value itself.
Thanks in advance.
Mark