I have a simple formula calculating eg in cell C1=A1*B1

Can someone please help me with a VBA code to keep formulas in cells in column C where cell values are 0. If cell value value become > than 0, I want to replace the formula in cell with the new cell value.

(This will ensure that a formula in a cell in column C is only calculated once?)Will appreciate any help

OvR

I think i understand what you are asking.... in each cell in column 'C' you have a formula that sum values in A and B?

if the sum is greater then 0 then replace that value in C with another value?

here is my attempt, in my case if sum of C > 0 then it will replace the value of C with 10.

Sub Check() Dim bCell As Range Dim Urange, Lrange Set Urange = Sheets("Sheet1").Range("C1") Set Lrange = Sheets("Sheet1").Range(Range("C" & Rows.Count).End(xlUp).Address) For Each bCell In Range(Urange, Lrange) If bCell.Value > 0 Then bCell.Value = 10 End If Next bCell End Sub

The OP said:

If cell value become > than 0, I want to replace the formula in cell with the new cell value.I believe that this line...

bCell.Value = 10...should be...

bCell.Value = bCell.ValueThis will replace the formula with the value returned by the formula as the OP requested.

To the OP:The code above needs to be manually executed each time you want to replace the 0 values. If you want to have Ax*Bx placed in Column C automatically, you can do it without even using the formula. It all depends on how you want to trigger the code.For example, if the last entry before the calculation is done is placed in Column B, then this code will place Ax*Bx in Cx as soon as Bx is changed. Since the code is doing the multiplication, you don't need to put any formulas in Column C.

Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 2 Then Range("C" & Target.Row) = Range("A" & Target.Row) * Target End If End SubThere are lots of variations of that code that will place the value in Cx. It all depends on how you are using your spreadsheet.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

THX a lot. Two replies in combination exactly what I need.

Ask Your Question

Weekly Poll