Solved in vba keep formulas only in cells with value of 0

September 6, 2012 at 04:06:33
Specs: Windows 7
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


See More: in vba keep formulas only in cells with value of 0

Report •


#1
September 6, 2012 at 05:37:23
✔ Best Answer
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


Report •

#2
September 6, 2012 at 06:48:32
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.Value

This 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 Sub

There 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.


Report •

#3
September 9, 2012 at 23:47:47
THX a lot. Two replies in combination exactly what I need.

Report •
Related Solutions


Ask Question