Macro to Replace Cell References with Formulas

by
macrosvba

When creating a complex formula sometimes you use multiple cells to get to a desired result. Then at the end you may want to join all those cells into one large formula. You can do this by copying formulas to replace the cell references but it’s not as easy as it should be.

The macro below does exactly that – it will replace the cell references with the formula in the referenced cell. For example, in cell A1 I have a simple formula of =5*5. 
In cell A2 the formula is =A1+10. 

What I do next is to select cell A1 and run the macro. It will look in the spreadsheet to see everywhere that cell A1 is referenced and replace it with the formula in A1. Below is the result in cell A2 after the macro has been run:

You’ll notice I added parentheses to make sure that the result would not get altered – not applicable in this example since order of operations would ensure the calculation is correct but in a more complex example it might not be the case.
Below is the code for this macro:
Sub copyformula()

Dim whattocopy As String
Dim whattoreplace As String

whattoreplace = Replace(ActiveCell.Address, “$”, “”)
whattocopy = “(” & Replace(ActiveCell.Formula, “=”, “”) & “)”

   Cells.Replace What:=whattoreplace, Replacement:=whattocopy, LookAt:=xlPart, _
       SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
       ReplaceFormat:=False
    
   Cells.Replace What:=ActiveCell.Address, Replacement:=whattocopy, LookAt:=xlPart, _
       SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
       ReplaceFormat:=False

End Sub

Add a Comment

You must be logged in to post a comment