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

R

rwilliams09

New Member
Joined
Jun 18, 2017
Messages
48
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?
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Sort by date Sort by votes
Hi
try this method some how

Code: 
=(ADDRESS(ROW(),COLUMN()))
=(ADDRESS(ROW()-1,COLUMN()+5))
......
 
Last edited:
Upvote 0
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:
Upvote 0
rwilliams09 said:
How do I use that macro to tell it which cells or which cell references?
Click to expand...
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.
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
rwilliams09 said:
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?
Click to expand...
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
 
Upvote 0
Hi Rick, thanks for the excellent code. I am using office 365 (desktop version) and am having following error:

When I run the code for Sum formula (e.g. Sum(G28:H28)) the output is =Sum(944:H28)

Saracote TP adjustment-Jun 2021.xlsx
GHI
24JunJulJun+Jul
25EstEstEst
26945889=SUM(944.529334672823:H26)
27336306=SUM(335.819688752319:H27)
Sheet2
Cell Formulas
RangeFormula
G26G26=428.455130266647*(2.2045)
H26H26=403.307714380417*(2.2045)
G27G27=152.333721366441*(2.2045)
H27H27=138.672621023516*(2.2045)
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Solved
#This Row reference from Text
Replies
3
Views
58
StephenCrump
S
datatronics505
  • Solved
Absolute reference to a cell of another table
Replies
5
Views
119
datatronics505
datatronics505
H
  • Question Question
Multiple user input value reference
Replies
6
Views
113
Hapakaya
H
T
  • Solved
Using reference data from one table to populate another column/table
Replies
2
Views
247
thirdeye85
T
P
  • Solved
Macro which changes the inside formula
2
Replies
18
Views
201
PetarTen
P

Forum statistics

Threads
1,223,705
Messages
6,173,986
Members
452,541
Latest member
haasro02

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top