Results 1 to 9 of 9

Thread: Replacing a cell reference within a formula with the hard-coded reference
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jun 2017
    Posts
    39
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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?

  2. #2
    Board Regular
    Join Date
    Mar 2015
    Location
    Syria
    Posts
    247
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Replacing a cell reference within a formula with the hard-coded reference

    Hi
    try this method some how

    Code:
    =(ADDRESS(ROW(),COLUMN()))
    =(ADDRESS(ROW()-1,COLUMN()+5))
    ......
    Last edited by mohadin; Sep 16th, 2019 at 12:03 AM.

  3. #3
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,224
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

    Default Re: Replacing a cell reference within a formula with the hard-coded reference

    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 by Rick Rothstein; Sep 16th, 2019 at 01:35 AM.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  4. #4
    New Member
    Join Date
    Jun 2017
    Posts
    39
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Replacing a cell reference within a formula with the hard-coded reference

    How do I use that macro to tell it which cells or which cell references?

  5. #5
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,224
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

    Default Re: Replacing a cell reference within a formula with the hard-coded reference

    Quote Originally Posted by rwilliams09 View Post
    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.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  6. #6
    New Member
    Join Date
    Jun 2017
    Posts
    39
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Replacing a cell reference within a formula with the hard-coded reference

    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 by rwilliams09; Sep 16th, 2019 at 02:57 PM.

  7. #7
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,224
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

    Default Re: Replacing a cell reference within a formula with the hard-coded reference

    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
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  8. #8
    New Member
    Join Date
    Jun 2017
    Posts
    39
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Replacing a cell reference within a formula with the hard-coded reference

    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?

  9. #9
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,224
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

    Default Re: Replacing a cell reference within a formula with the hard-coded reference

    Quote Originally Posted by rwilliams09 View Post
    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
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •