How do I do a VBA comparison to a financial value (i.e. $10000)

It's been a while since I've used VBA. How do I do a
comparison with a financial value?

I have the following, but no matter what the value
of the cell this puts a 1 into the active cell.

If ActiveCell.Offset(0, -1).Value > "$10,000" Then
ActiveCell.Value = 1
End If

But, no matter what the value of the
ActiveCell.Offset(0, -1).Value
this puts a 1 into the active cell, when I only want a 1
when the ActiveCell.Offset(0, -1).Value is > $10,000

Thanks,

Norm

How do I do a VBA comparison to a financial value (i.e. $10000)

You're pretty close. Try:

If ActiveCell.Offset(0, -1).Value > 10,000 Then
ActiveCell.Value = 1
End If

The formatting really doesn't make a difference when you use .value
(unless it is an actual text value and not a number)

On 27 Mar 2006 21:17:30 -0800, "Kletcho" <[email protected]> wrote:

~You're pretty close. Try:
~
~ If ActiveCell.Offset(0, -1).Value > 10,000 Then
~ ActiveCell.Value = 1
~ End If
~
~The formatting really doesn't make a difference when you use .value
~(unless it is an actual text value and not a number)

Wow, thanks for the speedy reply!

Unfortunately, it didn't work, I get a compile error. The data
I'm comparing is formatted as currency. My complete formula is:

Sub Macro13()
Range("H1").Select
Do Until ActiveCell.Offset(3, -1) = ""
If ActiveCell.Offset(0, -1).Value > 10,000 Then
ActiveCell.Value = 1
End If
ActiveCell.Offset(1, 0).Select
Loop
End Sub

This brings up a compile error. The data I'm using looks like this.

$12,330 J1
$12,314 J2
$9,608 J3
$9,245 J4

So I scan down column J and if the value in the corresponding cell
of column H is > $10000 I want to put a 1 in column J, otherwise
leave column J blank. The data should look like this when done.

$12,330 1
$12,314 1
$9,608 BLANK
$9,245 BLANK

TIA for any further help you can provide.

Looks like it works if I leave the comma out. Ie. 10000, not 10,000

Thanks again for your help.

Norm
__________________________________________________________________

On Tue, 28 Mar 2006 05:42:18 GMT, Father Guido <[email protected]> wrote:

~On 27 Mar 2006 21:17:30 -0800, "Kletcho" <[email protected]> wrote:
~
~~You're pretty close. Try:
~~
~~ If ActiveCell.Offset(0, -1).Value > 10,000 Then
~~ ActiveCell.Value = 1
~~ End If
~~
~~The formatting really doesn't make a difference when you use .value
~~(unless it is an actual text value and not a number)
~
~Wow, thanks for the speedy reply!
~
~Unfortunately, it didn't work, I get a compile error. The data
~I'm comparing is formatted as currency. My complete formula is:
~
~Sub Macro13()
~ Range("H1").Select
~ Do Until ActiveCell.Offset(3, -1) = ""
~ If ActiveCell.Offset(0, -1).Value > 10,000 Then
~ ActiveCell.Value = 1
~ End If
~ ActiveCell.Offset(1, 0).Select
~ Loop
~End Sub
~
~This brings up a compile error. The data I'm using looks like this.
~
~$12,330 J1
~$12,314 J2
~$9,608 J3
~$9,245 J4
~
~So I scan down column J and if the value in the corresponding cell
~of column H is > $10000 I want to put a 1 in column J, otherwise
~leave column J blank. The data should look like this when done.
~
~$12,330 1
~$12,314 1
~$9,608 BLANK
~$9,245 BLANK
~
~TIA for any further help you can provide.

Glad you figured it out.