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

Question

Hello and thank you in advance for your help.

I have a VBA script that is highlighting a range of cells where some formulas are returning the #REF! error.

I was wondering if anyone would know how to write a for loop that:

Cycles through the selected cells and checks if the cell is returning the #REF! error (not all do).
If there is a #REF! error, the code replaces the “#REF!” portion of the formulas with the letter of the cell’s column immediately followed by a number. The number is contained in an integer variable called “Rev_Row.”
If there is no #REF! error, the code moves on to check the next cell for the error.

The letter and number combo that replace the “REF!” text creates a cell address. The referenced cell contains a number, which is the divisor for the formula that the “#REF!” is in (“=4/#REF!”).

Thanks again.

hansvogelaar · Accepted Answer

jmw_87&nbsp;
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 &amp; Rev_Row)
            End If
        Next cel
        Application.ScreenUpdating = True
    End If
End Sub

Forum Discussion

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

2 Replies

Resources