Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I do a VBA comparison to a financial value (i.e. $10000)
On 27 Mar 2006 21:17:30 -0800, "Kletcho" 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I do a VBA comparison to a financial value (i.e. $10000)
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 wrote: ~On 27 Mar 2006 21:17:30 -0800, "Kletcho" 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I do a VBA comparison to a financial value (i.e. $10000)
Glad you figured it out.
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
COUNTIF calculates slow (10000+ records) | Excel Worksheet Functions | |||
preparation of financial performance and financial position ? | New Users to Excel | |||
take out comma ID, 10000 to ID 100000 in column | Excel Discussion (Misc queries) | |||
divide values by 10000 | New Users to Excel | |||
When I type in 100 a 1 appears, 10000 and 100 appear | Excel Discussion (Misc queries) |