[VBA] Is it possible to convert all of the individual ranges from a specific cell formula and paste these as values

[x0nar]

Sorry for not being able to explain correctly in the thread topic. I'll try my best to detail down the problem below.


Now what I would like the VBA code to do is to paste the ultimate precedent value instead of directly pasting the answer.

Consider cells in the below table (A1 as 10, A2 as 20, etc)

A	B	D
10	40	70
20	50	80
30	60	90

Now Cells A4, B4, and D4 are basically cells that sum the first 3 cells of that column. For instance, the formula in Cell A4 is

SUM(A1:A3)

Let's say, I have a function in Cell E4, which contains the formula

SUM(A4, B4, D4)

Now if I keep E4 as an active cell, the function should return the output as

(10+20+30)+(40+50+60)+(70+80+90)

The brackets are not mandatory
Not as

5500

, and not even as

60 + 150 + 240

(i.e. the values of Cell A4, B4, and D4)

The above example was pretty straight forward to do since it only contains a simple sum formula.

However let's say if I have certain other formulas wherein there are Index Matchs or Offsets, it would be complicated. Also in the above formula, there were just two layers, i.e. from E4 to A4 ... and from A4 to A1.... etc. Now there could also be more layers. e.g. Instead of A1 being a plain value it could be a sum of X1 and Y1. So, the most core value of any range should be pasted.

Thus I would like the ranges in a specific formula of the cell to be broken down as their most primitive values, and retain other functions as it is. You could see something like this if you use the Evaluate Formula option from the "Formula Auditing" tool of Excel

I just wanted to check if something like this was possible to do using some VBA feature I was unaware of? I'm not even asking for you to write code, I'll try to write it by myself. I just need someone to point me in the right direction.

Please feel free to reply if you do not understand my problem.

 

[JLGWhiz]

The formula in a cell will either return a value that the formula calls for or an error code that indicates the formula could not derive a logical value. You can manually change the content of the cell by selecting the cell, clicking Copy, then PasteSpecial>Values, which removes the formula and replaces it with whatever the displayed value of the cell was. You can use vba to do the same replacement process for any cell with a formula. But bear in mind that if there are other cells which are dependent on or precedent to the value of that cell, they will then be using a static value for the cell where the formula was removed. Not sure this answers your question, but it is how formulas can be changed to values.

 

[x0nar]

The formula in a cell will either return a value that the formula calls for or an error code that indicates the formula could not derive a logical value. You can manually change the content of the cell by selecting the cell, clicking Copy, then PasteSpecial>Values, which removes the formula and replaces it with whatever the displayed value of the cell was. You can use VBA to do the same replacement process for any cell with a formula. But bear in mind that if there are other cells that are dependent on or precedent to the value of that cell, they will then be using a static value for the cell where the formula was removed. Not sure this answers your question, but it is how formulas can be changed to values.

That's what my problem is.
I have a ton of cells that contain formulas. Some simple, some complex. Now my task is that in the adjacent row/column, I'd like to break these formulas down to their ultimate values, such that nothing apart from operators would be allowed. e.g.

SUM(A1,A2,A3)*20%

should look like

(10+20+30)*20%

Do you think this is achievable using VBA?

 

[JLGWhiz]

You would have to enter the data as text to make it appear with individual cell values, because when Excel sees a + or - operator it immediately tries to combine the values. To do that you would enter

=A1&"+"&A2&"+"&A3&")="&SUM(A1,A2,A3)

 

[Rick Rothstein]

I am not sure if the following macro will work for you or not. What it does is replace individual cell references in all the cells in the current selection with their corresponding values... but it does not work with ranges of cells. So, if one of the selected cells contained this formula...

=SUM(A1,A2,A3)

it would work fine, but if the cell contained this...

=SUM(A2:A3)

it would not work. With that said, here is the code but note that it puts its output in the adjacent column (if you have data there, change the offset number I highlighted in red to a value that points to an empty column. Also read the note I put after the code as well.

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.