Autofilter - formatting criteria1 data for valid comparison
 

2003

Procedure obtains a numeric value (NumbToReplace) from an input box.

The typical worksheet column format is:
NumberFormat = "#,##0.00_);(#,##0.00)" 'Number with 2 Dec Places

But the same column on other worksheets have format of:
NumberFormat = "#,##0.00000_);(#,##0.00000)" 'Number with 5

I format NumbToReplace as follows:
FilterCriteria = WorksheetFunction.Text(NumbToReplace, _
"#,##0.00_);(#,##0.00)")

Then I set the filter:
Filter1.AutoFilter Field:=4, Criteria1:="=" & FilterCriteria

The challenge: How to format "NumbToReplace" which becomes
"FilterCriteria" so that the filter is set properly no matter how many
decimal places the worksheet column has?

I am having a can't-see-the-forest-for-the-trees-moment even though the
answer is probably staring me right in the face..

TIA EagleOne

Autofilter - formatting criteria1 data for valid comparison
 

Can you pick up the numberformat from one of the cells in that column?

Dennis wrote:

2003

Procedure obtains a numeric value (NumbToReplace) from an input box.

The typical worksheet column format is:
NumberFormat = "#,##0.00_);(#,##0.00)" 'Number with 2 Dec Places

But the same column on other worksheets have format of:
NumberFormat = "#,##0.00000_);(#,##0.00000)" 'Number with 5

I format NumbToReplace as follows:
FilterCriteria = WorksheetFunction.Text(NumbToReplace, _
"#,##0.00_);(#,##0.00)")

Then I set the filter:
Filter1.AutoFilter Field:=4, Criteria1:="=" & FilterCriteria

The challenge: How to format "NumbToReplace" which becomes
"FilterCriteria" so that the filter is set properly no matter how many
decimal places the worksheet column has?

I am having a can't-see-the-forest-for-the-trees-moment even though the
answer is probably staring me right in the face..

TIA EagleOne

--

Dave Peterson

Autofilter - formatting criteria1 data for valid comparison
 

DA!

Thanks Dave

Dave Peterson wrote:
Can you pick up the numberformat from one of the cells in that column?

Dennis wrote:

2003

Procedure obtains a numeric value (NumbToReplace) from an input box.

The typical worksheet column format is:
NumberFormat = "#,##0.00_);(#,##0.00)" 'Number with 2 Dec Places

But the same column on other worksheets have format of:
NumberFormat = "#,##0.00000_);(#,##0.00000)" 'Number with 5

I format NumbToReplace as follows:
FilterCriteria = WorksheetFunction.Text(NumbToReplace, _
"#,##0.00_);(#,##0.00)")

Then I set the filter:
Filter1.AutoFilter Field:=4, Criteria1:="=" & FilterCriteria

The challenge: How to format "NumbToReplace" which becomes
"FilterCriteria" so that the filter is set properly no matter how many
decimal places the worksheet column has?

I am having a can't-see-the-forest-for-the-trees-moment even though the
answer is probably staring me right in the face..

TIA EagleOne

--

Dave Peterson