Replace cell references with values without F9 using macro

Question

This is an Excel macro question. Given a series of formulas such as the following:

= A1 + A2
= B2 + B2

etc., ...

I would like to replace each of these cell references with the values without replacing the whole cell.

= 1 + 2
= 3 + 4

The way of doing this manually would be to highlight each reference within the formula and press F9 to replace the references with values. However, I have to do this for thousands of cells. Is there a macro to accomplish this automatically?

Answer 1

The following module takes a range and replaces cell references with values found in the referenced cells. Uses Range.Precedents to get the referenced cell addresses to a string and Split to transform it to an array of cell addresses. This array is then used to find and replace matching text in the formula of the target cell.

Usage

• Copy the entire code to a Standard Code Module in the Visual Basic Editor.
• Call the Subroutine with Call ReplaceReferencesWithValues(yourRange).

The Test macro calls the Subroutine to replace the references in the selected range.

Code

Option Explicit


Sub Test()
    Call ReplaceReferencesWithValues(Selection)
End Sub


Sub ReplaceReferencesWithValues(rng As Range)
    Dim cl As Range
    Dim ws As Worksheet
    Dim strTemp As String
    Dim strRef() As String
    Dim intIndex As Integer

    For Each cl In rng
        Set ws = cl.Worksheet
        strTemp = cl.Formula
        strRef() = ReferenceArray(cl)

        For intIndex = LBound(strRef) To UBound(strRef)
            strTemp = Replace(strTemp, strRef(intIndex), _
                ws.Range(strRef(intIndex)).Value)
        Next

        cl.Formula = strTemp
    Next
End Sub


Function ReferenceArray(rngSource As Range) As Variant
    Dim rngRef As Range
    Dim strTemp As String
    On Error Resume Next

    For Each rngRef In rngSource.Precedents.Cells
        strTemp = strTemp & ", " & rngRef.Address(False, False)
    Next
    If Len(strTemp) > 0 Then strTemp = Mid(strTemp, 3)

    ReferenceArray = Split(strTemp, ", ")
End Function

---

Concerns

• The replacement happens as a String and as such, if the formula would contain both a reference to the cell "A1" and other text including "A1", the matching text would also be replaced with the value of cell "A1".
• Formulas that require a range to function, such as SUM, will break if you attempt to replace the references with cell values.