Repost: NumberFormat/Autofilter/Rounding Problem

[Mark Driscol]

Please forgive the repost. I am hoping the reason for no response was
the lateness of the hour I posted this. If anyone has some
insight/guidance on this, I would sincerely appreciate it. Thanks in
advance.

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

 

[Bob Phillips]

have you tried

ActiveSheet.AutoFilter.Range.AutoFilter _
Field:=1, _
Criteria1:=ActiveCell.Text

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Mark Driscol]

Hello Bob,

Yes, this did work in this particular case, thank you. However, if the
value is 23.1515 formatted as

_($* #,##0_);_($* (#,##0);_($* "-"??_);_(@_)

it does not work. It seems the formatting somehow comes into play. In
this case, I can select "$23" from the dropdown menus, but I cannot use
ActiveCell.Text since, depending on the column width, we could have

ActiveCell.Text = $ 23, or
ActiveCell.TExt = $ 23, etc.

It still seems that there is an interaction between the value and the
formatting that prevents using VBA to set the filter.

Thanks again, Bob. Any other ideas that someone can share would be
most appreciated.

Mark

 

[Mark Driscol]

Well, it looks like this may work, in case someone else runs into this
problem in the future.

ActiveSheet.AutoFilter.Range.AutoFilter _
Field:=1, _
Criteria1:=Application.Substitute(ActiveCell.Text,"
","")

Thanks again, Bob, for heading me in the right direction.


Mark