Results 1 to 7 of 7

Thread: Replace Cell References In Formulas With Their Actual Value

  1. #1
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    659
    Rep Power
    13

    Replace Cell References In Formulas With Their Actual Value

    I am not sure how useful the actual functionality that the code below invokes, but I think the underlying techniques might prove useful for other coding endeavors you might undertake. Someone in another forum asked the following question (I am paraphrasing it here)...

    "If the formula in my cell has three different cell references all on different sheets, such as...

    =Sheet1!C3+Sheet2!F5+Sheet3!H7

    then how do I get it to display the same formula, but with the cell references replaced by the cell values? For example, if Sheet1!C3 contained 123 and Sheet2!F5 contained 456 and Sheet3!H7 contained 789, then how do I get this formula returned to me?

    =123+456+789

    The solution involves one of the object structures that I just cannot believe VBA could not have implemented in some better way. The macro solution involves displaying the Formula Auditing Trace Precedents Arrows from sheet to sheet and cell to cell, and then following (navigating) each arrow to its source worksheet and then iterating each cell that the navigation links to. While the macro's code is not really all that complex, the idea and actions to implement it were, well for lack of a better word, cumbersome. Here is the code I came up with, although I would not be surprised to learn there is better code available... be sure to read the note following it.

    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 replaced along with the actual cell reference in the formula... I do not know a way around this should it occur.

  2. #2
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    12
    There's another property
    Code:
    Sub M_snb()
          For Each it In Sheets("sheet3").Cells(1).Precedents
             x3 = it.Address
          Next
    End Sub

    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    Last edited by DocAElstein; 10-02-2023 at 12:54 PM.

  3. #3
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    659
    Rep Power
    13
    Quote Originally Posted by snb View Post
    There's another property
    Code:
    Sub M_snb()
          For Each it In Sheets("sheet3").Cells(1).Precedents
             x3 = it.Address
          Next
    End Sub
    That only works for cells on the same sheet as the formula whose Precedent Cells you are evaluating... the Precedents property does not directly identify cells located on other worksheets; hence, the need to use the NavigateArrow property in order to trace each line onto the worksheet it is pointing to.

  4. #4
    Member
    Join Date
    Dec 2012
    Posts
    43
    Rep Power
    0
    Rick,

    this is excelllent. i found it very useful.

    cheers,
    Last edited by Admin; 02-12-2014 at 07:42 AM. Reason: quote removed

  5. #5
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    659
    Rep Power
    13
    Quote Originally Posted by jamilm View Post
    Rick,

    this is excelllent. i found it very useful.
    Great! Thanks for letting me know.

  6. #6
    Member
    Join Date
    Dec 2012
    Posts
    43
    Rep Power
    0
    Hi Rick,

    while i testing this code. i faced the following problem. once i run the macro it only gets values from sheet2 not all three sheets. i have uploaded the file here https://skydrive.live.com/redir?resi...F--p4_QDGRNXkU

  7. #7
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    659
    Rep Power
    13
    Quote Originally Posted by jamilm View Post
    Hi Rick,

    while i testing this code. i faced the following problem. once i run the macro it only gets values from sheet2 not all three sheets. i have uploaded the file here https://skydrive.live.com/redir?resi...F--p4_QDGRNXkU
    jamilm, I must apologize as I completely missed this follow-up question of yours (you must have posted it during one of my "away times"). I just tried to download your file from the link you provided, but it apparently has been removed. If you still have this question, can you send the file directly to me (my computer has been having problems for awhile now... it's in serious need of having Windows reinstalled, but I keep putting off doing that)? My email addresss...

    rick DOT news AT verizon DOT net

    Please mention the thread name so I know what your attached file pertains to.

    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    Last edited by DocAElstein; 10-02-2023 at 12:55 PM.

Similar Threads

  1. Replies: 2
    Last Post: 07-04-2013, 12:37 PM
  2. VBA Using MID Function To Replace Portion Of A String
    By Transformer in forum Tips, Tricks & Downloads (No Questions)
    Replies: 1
    Last Post: 05-30-2013, 08:22 PM
  3. Replace Incorrect Date In Cell To Another Valid Date
    By DARSHANKmandya in forum Excel and VBA Tips and Tricks
    Replies: 2
    Last Post: 03-21-2013, 09:27 PM
  4. Date References to save files using VBA Code
    By mrmmickle1 in forum Excel Help
    Replies: 3
    Last Post: 11-28-2012, 05:48 PM
  5. Replace Currency Format From One to Another at One go !! (VBA)
    By Admin in forum Excel and VBA Tips and Tricks
    Replies: 0
    Last Post: 07-19-2011, 09:13 PM

Posting Permissions

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