Post the formula instead of value?

[NicoLandbo]

Hi,

Hope someone can help me with this. I thought it would be pretty simple to do, and I have tried alot without any luck. So now I come seeking some expertise.

Sub Explained_variance()

    Dim i As Long
    Dim BALlastrow As Long
    Dim v As Variant
    Dim LockedCell As Range
    Dim j As Long
    
    BALlastrow = Range("B" & Rows.Count).End(xlUp).Row
    
    For i = 6 To BALlastrow
        If Range("G" & i).Interior.Color <> Range("G" & i).Offset(1).Interior.Color And Range("G" & i).Interior.Color <> Range("G" & i).Offset(-1).Interior.Color Then
        Range("G" & i).FormulaR1C1 = "=IFERROR(RC[-2]/(ABS(RC[-2])),"""")"
        j = 1
        
        Else
        Set LockedCell = Range("E" & i - j)
            If LockedCell = 0 Then
            LockedCell = 0.0001
            End If
            
        With Application
            v = .IfError(Range("E" & i) / Abs(LockedCell), "")
            Range("G" & i).Formula = v
        End With
        
        j = j + 1
        
        End If
    Next i
    
End Sub

This is my sub. It works as intended, but this area in particular post the value of the formula, instead of including the formula:

With Application            v = .IfError(Range("E" & i) / Abs(LockedCell), "")
            Range("G" & i).Formula = v
        End With

Can anyone help me with what I should change in the above code?

 

[kvsrinivasamurthy]

Replace this

With Application
            v = .IfError(Range("E" & i) / Abs(LockedCell), "")
            Range("G" & i).Formula = v
        End With

By

Range("G" & i).Formula = "=IFERROR(E" & i & "/ABS(E" & i - j & "),"""")"

 

[NicoLandbo]

Replace this

With Application
            v = .IfError(Range("E" & i) / Abs(LockedCell), "")
            Range("G" & i).Formula = v
        End With

By

Range("G" & i).Formula = "=IFERROR(E" & i & "/ABS(E" & i - j & "),"""")"

Thank you. Works like a charm.