VBA using a for loop to replace portions of formulas in selected range

jmw_87 

Try this:

Sub ReplaceREF()
    ' Replacve this with your own code for Rev_Row
    Dim Rev_Row As Long
    Rev_Row = 37

    Dim rng As Range
    Dim cel As Range
    Dim col As String
    On Error Resume Next
    Set rng = Selection.SpecialCells(Type:=xlCellTypeFormulas, Value:=xlErrors)
    On Error GoTo 0
    If Not rng Is Nothing Then
        Application.ScreenUpdating = False
        For Each cel In rng
            If cel.Value = CVErr(xlErrRef) Then
                col = Split(cel.Address, "$")(1)
                cel.Formula = Replace(cel.Formula, "#REF!", col & Rev_Row)
            End If
        Next cel
        Application.ScreenUpdating = True
    End If
End Sub