vb if formula

Zack Barresse

MrExcel MVP
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?

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
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

i want to take the existing value,

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

Zack, try this

Sub test()

Dim Zack
Zack = ActiveCell.Value

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

End Sub

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?

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

With Selection

with (for example)

With Range("A1:D50")

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

thanks Tom, works like a charm.

appreciate the help guys!

Replies
7
Views
174
Replies
15
Views
764
Replies
8
Views
652
Replies
3
Views
309
Replies
3
Views
334

1,203,323
Messages
6,054,728
Members
444,747
Latest member
Jaborsum

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back