Filter currency by variable

[mdocton]

Greetings,

I have tried numerous options and searched for a solution, but to no avail. I'd be very grateful for any help...

I have a macro does many things but one small part fails;

I have column H formatted as Currency.
I have a variable (AmountSought) dimmed as Currency
The macro filters on Column H for AmountSought.

It will, however, only find positive numbers. So, if the amount sought is £5,000, it won't find -£5,000.
I have been trying;
ActiveSheet.Range("A:M").AutoFilter Field:=10, Criteria1:=AmountSought, _
Operator:=xlOr, Criteria2:=AmountSought2
where AmountSought2 = 0 - AmountSought

I have stopped the macro there and the custom filter is looking for ($5,000.00)

Can anyone suggest how I could treat the variable or Filter differently so that it'll find 5000 And -5000?

Many thanks

Mark

 

[repairman615]

Hello,

Here may be an idea.
Try the ABS. set the variable to the absolute value and then check for the abs. value.

Vba help file:

<TABLE id=topTable width="100%"><TBODY><TR id=headerTableRow1><TD align=left></TD></TR><TR id=headerTableRow2><TD align=left>Abs Function</TD></TR></TBODY></TABLE>
Returns a value of the same type that is passed to it specifying the absolute value of a number.
Syntax
Abs(number)
The required number argument can be any valid numeric expression. If number contains Null, Null is returned; if it is an uninitialized variable, zero is returned.
Remarks


The absolute value of a number is its unsigned magnitude. For example, <TABLE><TBODY><TR><TD>

<CODE>ABS(-1)</CODE>
</PRE></TD></TR></TBODY></TABLE></P>and

<TABLE><TBODY><TR><TD>

<CODE>ABS(1)</CODE>
</PRE></TD></TR></TBODY></TABLE></P>both return

<TABLE><TBODY><TR><TD>

<CODE>1</CODE>
</PRE></TD></TR></TBODY></TABLE></P>.

Example

This example uses the Abs function to compute the absolute value of a number.

<TABLE><TBODY><TR><TD>

<CODE>Dim MyNumberMyNumber = Abs(50.3) ' Returns 50.3.MyNumber = Abs(-50.3) ' Returns 50.3.</CODE>
</PRE></TD></TR></TBODY></TABLE></P>

 

[mdocton]

Thanks for your reply

I need to be able to use the Filter as the results are copied out to another workbook. The searched sheet then closes and it moves on to check the next.

The variable is input by the user who is looking for any value of 5000, whether it's a credit or debit.
I can how using Abs would work if the user put in -5000; it would then search for 5000 or -5000.
The problem is the way the data to be seached is recorded.

I've tried reformatting Col H as a number or text (not currency) and declaring the variable in a different way, but have failed so far!

I could add add a column (Col I), add an Abs formula so, for example, I7 = Abs(H7) and filter on I. That'd work.
With large workbooks, however, that may slow it down a bit.

Thanks for you tip - I'll work on that ...

 

[mdocton]

Cracked it!

Columns("H:J").Select
Selection.NumberFormat = "$#,##0.00;[Red]$#,##0.00"

AutoFilter then picks up the negatives.

Marvellous - I spent ages working on it. Thanks again for your time.

Mark