Replacing a cell reference within a formula with the hard-coded reference

rwilliams09

New Member
Joined
Jun 18, 2017
Messages
39
Hi, I have a huge table with hundreds of values and then another table with formulas and inside the formulas are a reference to the values in the first table.

I would like to have the values (each of which is different) inside the formula instead of having two tables. However, it would take me FOREVER to manually do this.

Is there a workaround to make this happen?
 

mohadin

Active Member
Joined
Mar 22, 2015
Messages
354
Office Version
2013
Platform
Windows
Hi
try this method some how

Code:
=(ADDRESS(ROW(),COLUMN()))
=(ADDRESS(ROW()-1,COLUMN()+5))
......
 
Last edited:

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,395
Office Version
2010
Platform
Windows
This might do what you want. The following macro will replace all cell reference in with the value at the referenced cells within the formula of all cells within the current Selection. This code will not be fast if you have a lot of cells within the Selection, so be prepared to wait a bit.
Code:
Sub CheckCellReferences()
  Dim ShapeCount As Long, Arrow As Long, Link As Long, Addr As String, Frmla As String
  Dim Cell As Range, CurrentCell As Range, OriginalSheet As String, OriginalCell As String
  Application.ScreenUpdating = False
  OriginalSheet = ActiveSheet.Name
  OriginalCell = ActiveCell.Address
  ShapeCount = ActiveSheet.Shapes.Count
  For Each Cell In Selection
    Set CurrentCell = Cell
    Frmla = Replace(CurrentCell.Formula, "$", "")
    If CurrentCell.HasFormula Then
      CurrentCell.ShowPrecedents
      Link = 1
      For Arrow = 1 To ActiveSheet.Shapes.Count - ShapeCount
        On Error Resume Next
        Do
          CurrentCell.Parent.Activate
          CurrentCell.Activate
          Addr = CurrentCell.NavigateArrow(True, Arrow, Link).Address
          If Err.Number Then
            Link = 1
            Exit Do
          End If
          Frmla = Replace(Frmla, ActiveCell.Address(0, 0), ActiveCell.Value)
          Frmla = Replace(Frmla, ActiveCell.Parent.Name & "!", "")
          Frmla = Replace(Frmla, "'" & ActiveCell.Parent.Name & "'!", "")
          Link = Link + 1
Continue:
        Loop
        Cell.Offset(, 1) = Frmla
      Next
      CurrentCell.ShowPrecedents Remove:=True
    End If
    Worksheets(OriginalSheet).Activate
    Range(OriginalCell).Activate
  Next
  Application.ScreenUpdating = False
End Sub
NOTE: If your formula has a text value that looks like a cell reference, for example the A12 in "Serial Number A12-345", and one of the cell references in the formula is actually A12, then the A12 inside the text constant will be replace along with the actual cell reference in the formula... I do not know a way around this should it occur.
 
Last edited:

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,395
Office Version
2010
Platform
Windows
How do I use that macro to tell it which cells or which cell references?
Select the cells you want to process first, then run the macro. The macro will replace every cell reference with that cell reference's value within every formula within the selected cells.
 

rwilliams09

New Member
Joined
Jun 18, 2017
Messages
39
I think what it is doing works; however, it is giving me the cell reference one column to the left every time. For example, if the formula has cell G11 referenced, it is returning the value in F11.

Is it because of the cell offset?
 
Last edited:

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,395
Office Version
2010
Platform
Windows
I had written that code several years ago and forgot that the person I wrote it for wanted to preserve the original formulas and have the "replacement" formula next to it. In his case, the original code worked only for a column of cells. I have fixed all the parts that would allow the code to overwrite the original cells and, while I was at it, generalize the code so it could handle a contiguous rectangular range of cells if need be. Here is the revised code (note that I changed the macro's name from that which I used on my original code)...
Code:
Sub ReplaceCellReferencesWithTheirValues()
  Dim R As Long, C As Long, ShapeCount As Long, Arrow As Long, Link As Long
  Dim Addr As String, OriginalSheet As String, OriginalCell As String, Frmla() As String
  Dim Cell As Range, CurrentCell As Range, OriginalSelection As String
  Application.ScreenUpdating = False
  OriginalSheet = ActiveSheet.Name
  OriginalCell = ActiveCell.Address
  OriginalSelection = Selection.Address
  ShapeCount = ActiveSheet.Shapes.Count
  ReDim Frmla(1 To Selection.Rows.Count, 1 To Selection.Columns.Count)
  For R = 1 To UBound(Frmla, 1)
    For C = 1 To UBound(Frmla, 2)
      Set CurrentCell = Range(OriginalSelection)(1).Offset(R - 1, C - 1)
      Frmla(R, C) = Replace(CurrentCell.Formula, "$", "")
      If CurrentCell.HasFormula Then
        CurrentCell.ShowPrecedents
        Link = 1
        For Arrow = 1 To ActiveSheet.Shapes.Count - ShapeCount
          On Error Resume Next
          Do
            CurrentCell.Parent.Activate
            CurrentCell.Activate
            Addr = CurrentCell.NavigateArrow(True, Arrow, Link).Address
            If Err.Number Then
              Link = 1
              Exit Do
            End If
            Frmla(R, C) = Replace(Frmla(R, C), ActiveCell.Address(0, 0), ActiveCell.Value)
            Frmla(R, C) = Replace(Frmla(R, C), ActiveCell.Parent.Name & "!", "")
            Frmla(R, C) = Replace(Frmla(R, C), "'" & ActiveCell.Parent.Name & "'!", "")
            Link = Link + 1
Continue:
          Loop
        Next
        CurrentCell.ShowPrecedents Remove:=True
      End If
      Worksheets(OriginalSheet).Activate
      Range(OriginalCell).Activate
    Next
  Next
  Range(OriginalSelection).Formula = Frmla
  Application.ScreenUpdating = False
End Sub
 

rwilliams09

New Member
Joined
Jun 18, 2017
Messages
39
Hi, now it is just showing all of the formulas instead of the results. Would I have to go one-by-one and press Enter? Any way around this?
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,395
Office Version
2010
Platform
Windows
Hi, now it is just showing all of the formulas instead of the results. Would I have to go one-by-one and press Enter? Any way around this?
I think this modified version of my modified version should now do what you want...
Code:
Sub ReplaceCellReferencesWithTheirValues()
  Dim R As Long, C As Long, ShapeCount As Long, Arrow As Long, Link As Long
  Dim Addr As String, OriginalSheet As String, OriginalCell As String, Frmla() As String
  Dim Cell As Range, CurrentCell As Range, OriginalSelection As String
  Application.ScreenUpdating = False
  OriginalSheet = ActiveSheet.Name
  OriginalCell = ActiveCell.Address
  OriginalSelection = Selection.Address
  ShapeCount = ActiveSheet.Shapes.Count
  ReDim Frmla(1 To Selection.Rows.Count, 1 To Selection.Columns.Count)
  For R = 1 To UBound(Frmla, 1)
    For C = 1 To UBound(Frmla, 2)
      Set CurrentCell = Range(OriginalSelection)(1).Offset(R - 1, C - 1)
      Frmla(R, C) = Replace(CurrentCell.Formula, "$", "")
      If CurrentCell.HasFormula Then
        CurrentCell.ShowPrecedents
        Link = 1
        For Arrow = 1 To ActiveSheet.Shapes.Count - ShapeCount
          On Error Resume Next
          Do
            CurrentCell.Parent.Activate
            CurrentCell.Activate
            Addr = CurrentCell.NavigateArrow(True, Arrow, Link).Address
            If Err.Number Then
              Link = 1
              Exit Do
            End If
            Frmla(R, C) = Replace(Frmla(R, C), ActiveCell.Address(0, 0), ActiveCell.Value)
            Frmla(R, C) = Replace(Frmla(R, C), ActiveCell.Parent.Name & "!", "")
            Frmla(R, C) = Replace(Frmla(R, C), "'" & ActiveCell.Parent.Name & "'!", "")
            Link = Link + 1
Continue:
          Loop
        Next
        CurrentCell.ShowPrecedents Remove:=True
      End If
      Worksheets(OriginalSheet).Activate
      Range(OriginalCell).Activate
    Next
  Next
  With Range(OriginalSelection)
    .Formula = Frmla
    .Value = .Value
  End With
  Application.ScreenUpdating = False
End Sub
 

Forum statistics

Threads
1,077,674
Messages
5,335,605
Members
399,028
Latest member
greyland

Some videos you may like

This Week's Hot Topics

Top