vb if formula

[Zack Barresse]

hello all, another question for ya,

i'm trying to figure out a way to write some code in which a range of cells can have a mathematical operation performed on them, only if their cell value is > 0. these cells are the byproduct of about seven or eight calculations.

i'd like to assign this macro to a button so i have the power to say when to start the calculation.
so basically, look at each cell in range, if > 0, then value * 0.7 (minus 30%). those that are zero,i do not wish to perform any calculations on.
hope this isn't too vague. anybody point me in the right direction?

 

[brucesw]

Hi Zack,

the following is an example of a formula I recorded and use frequently. It tests the date value of the cell to the left of the activecell. Did you mean to create a formula in a cell or enter the result as a value? the following will leave a formula.

Sub My_formula()

ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]=DATE(1997,2,1),""pre-WCA"",""WCA"")"

end sub

Cheers, Bruce

 

[Zack Barresse]

i want to take the existing value,
adjust accordingly,
and leave adjusted as value.

 

[Tom Urtis]

Assuming


- The numbers are constants, not returned by formulas
- You want to select the range first, to have the control you mention


Sub Test1()
With Selection
Dim x As Range
For Each x In .SpecialCells(xlCellTypeConstants, 1)
If x.Value > 0 Then
x.Value = x.Value * 0.7
End If
Next x
End With
End Sub

 

[brucesw]

Zack, try this

Sub test()

Dim Zack
Zack = ActiveCell.Value

If Zack > 0 Then
ActiveCell.Value = Zack * 0.7
End If

End Sub

 

[Zack Barresse]

Tom,
i got your code to work ok.

brucesw,
i'm sorry, i couldn't get yours to work.

one more question guys,
to change With Selection to a known range, i think my verbage is off - how would you do that?

 

[Tom Urtis]

For my code if that is what you are referring to, substitute

With Selection


with (for example)

With Range("A1:D50")

 

[Tom Urtis]

You can also omit the With structure entirely if you do not plan to refer to that range later, like so:

Sub Macro2()
Dim x As Range
For Each x In Range("A1:D50").SpecialCells(xlCellTypeConstants, 1)
If x.Value > 0 Then
x.Value = x.Value * 0.7
End If
Next x
End Sub

 

[Zack Barresse]

thanks Tom, works like a charm.

appreciate the help guys!