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

#### x0nar

##### New Member
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
Excel Formula:
``SUM(A1:A3)``

Let's say, I have a function in Cell E4, which contains the formula
Excel Formula:
``SUM(A4, B4, D4)``

Now if I keep E4 as an active cell, the function should return the output as
Code:
``(10+20+30)+(40+50+60)+(70+80+90)``
The brackets are not mandatory
Not as
Excel Formula:
``5500``
, and not even as
Excel Formula:
``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.

Last edited:

### Excel Facts

If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

#### JLGWhiz

##### Well-known Member
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

##### New Member
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.
VBA Code:
``SUM(A1,A2,A3)*20%``
should look like
VBA Code:
``(10+20+30)*20%``

Do you think this is achievable using VBA?

#### JLGWhiz

##### Well-known Member
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
Excel Formula:
``=A1&"+"&A2&"+"&A3&")="&SUM(A1,A2,A3)``

#### Rick Rothstein

##### MrExcel MVP
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.
Rich (BB 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
ShapeCount = ActiveSheet.Shapes.Count
For Each Cell In Selection
Set CurrentCell = Cell
Frmla = Replace(CurrentCell.Formula, "\$", "")
If CurrentCell.HasFormula Then
CurrentCell.ShowPrecedents
For Arrow = 1 To ActiveSheet.Shapes.Count - ShapeCount
On Error Resume Next
Do
CurrentCell.Parent.Activate
CurrentCell.Activate
If Err.Number Then
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 & "'!", "")
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.

Replies
1
Views
57
Replies
1
Views
66
Replies
0
Views
26
Replies
7
Views
172
Replies
0
Views
55