I have some VBA which adds formulas to cells if the value in column B is greater than 0. Is there a way to clear these formulas from the cells if the value in column B is equal to 0?
Code for adding the formulas is below.
Sub AddFormulas()
Application.ScreenUpdating = False
Dim r As Range
Dim i As Range
Set r = Range("B25", Range("B65536").End(xlUp))
For Each i In r
If i > 0 Then
With i.Offset(0, 10)
.FormulaR1C1 = "=IF(COUNTA(RC[8]),0,HLOOKUP(RC[-9],CP_Circuits_Lookup,2,FALSE))"
End With
With i.Offset(0, 11)
.FormulaR1C1 = "=IF(COUNTA(RC[8]),0,HLOOKUP(RC[-10],CP_Circuits_Lookup,3,FALSE))"
End With
With i.Offset(0, 12)
.FormulaR1C1 = "=IF(COUNTA(RC[8]),0,HLOOKUP(RC[-11],CP_Circuits_Lookup,4,FALSE))"
End With
With i.Offset(0, 13)
.FormulaR1C1 = "=IF(COUNTA(RC[8]),0,HLOOKUP(RC[-12],CP_Circuits_Lookup,5,FALSE))"
End With
With i.Offset(0, 14)
.FormulaR1C1 = "=IF(ISERROR(RC[-4]+RC[4]),0,(RC[-4]+RC[4]))"
End With
With i.Offset(0, 15)
.FormulaR1C1 = "=IF(ISERROR(RC[-4]+RC[4]),0,(RC[-4]+RC[4]))"
End With
With i.Offset(0, 16)
.FormulaR1C1 = "=IF(ISERROR(RC[-4]+RC[4]),0,(RC[-4]+RC[4]))"
End With
With i.Offset(0, 17)
.FormulaR1C1 = "=IF(ISERROR(RC[-4]+RC[4]),0,(RC[-4]+RC[4]))"
End With
Else: i.Offset(0, 2) = 0
End If
Next i
Application.ScreenUpdating = True
End Sub
Bookmarks