Macro for adding IF statements to the existing formula

[HariHaran R S]

Hi All,

is there a Macro for adding If Formula to all the cells that are present in a selected range. For example I have a main datasheet where the data's are cell reference to sub data sheet. Now I want to add IF statement to all the cells instructing it to give a blank cell if the value present in the cell referenced is zero and give the value in the cell if the cell referenced has a value in it ? Attached is a image of the formula that should look like when if statements get added

 

[CephasOz]

You could try this:

Sub ChangeFormula()
    Const clngCol As Long = 4
    Dim lngRow As Long
    For lngRow = 10 To 17
        With ActiveSheet.Cells(lngRow, clngCol)
            If .HasFormula Then
                .FormulaR1C1 = "=LET(x," & Mid(.FormulaR1C1, 2) & ",IF(x=0,"""",x))"
            End If
        End With
    Next
End Sub

Note: you have to change the clngCol value to the number of the column, and the "For lngRow = 10 to 17" to reflect your row numbers.