Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old May 29th 12, 06:23 PM
Junior Member
 
First recorded activity by ExcelBanter: May 2012
Posts: 3
Default Display formulae as values of each cell reference

Hello all, I was wondering if someone could please help me with a macro. Any and all help is appreciated:

Essentially, it would be a macro where you take a cell (which has various formulas and cell references in it) and convert those formulas and references into their values within the formula bar into a separate cell next to it.

For example:

Where Cell A1 = Cells (F2+F3+F4)/F5
F2 = 50
F3= 200
F4= 250
F5=100

Steps for Macro

1. I would highlight cell A1 (or if multiple cells, I would highlight all of them)
2. I would run the macro

Macro Output

1. The macro would paste a formula into the corresponding cells directly to the right of it. (In this case, it would be cell B1)
2. In cell B1, the cell would show a value of "5", and would show the following formula: =(50+200+250)/100

  #2   Report Post  
Old May 30th 12, 05:22 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2003
Posts: 509
Default Display formulae as values of each cell reference

plotzmoney wrote:

Hello all, I was wondering if someone could please help me with a macro.
Any and all help is appreciated:

Essentially, it would be a macro where you take a cell (which has
various formulas and cell references in it) and convert those formulas
and references into their values within the formula bar into a separate
cell next to it.

For example:

Where Cell A1 = Cells (F2+F3+F4)/F5
F2 = 50
F3= 200
F4= 250
F5=100

Steps for Macro

1. I would highlight cell A1 (or if multiple cells, I would highlight
all of them)
2. I would run the macro

Macro Output

1. The macro would paste a formula into the corresponding cells directly
to the right of it. (In this case, it would be cell B1)
2. In cell B1, the cell would show a value of "5", and would show the
following formula: =(50+200+250)/100


As it happens, I solved a similar problem for myself some time ago. Try this:

Sub SelectionFormula2Value()
Dim cell As Range
For Each cell In Selection.Cells
cell.Offset(0, 1).Formula = "'" & cell.Formula
Next
End Sub

--
Since all the ideas involve you, I'm not suprised.
  #3   Report Post  
Old May 30th 12, 12:26 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2010
Posts: 1,045
Default Display formulae as values of each cell reference

On Tue, 29 May 2012 17:23:23 +0000, plotzmoney wrote:


Hello all, I was wondering if someone could please help me with a macro.
Any and all help is appreciated:

Essentially, it would be a macro where you take a cell (which has
various formulas and cell references in it) and convert those formulas
and references into their values within the formula bar into a separate
cell next to it.

For example:

Where Cell A1 = Cells (F2+F3+F4)/F5
F2 = 50
F3= 200
F4= 250
F5=100

Steps for Macro

1. I would highlight cell A1 (or if multiple cells, I would highlight
all of them)
2. I would run the macro

Macro Output

1. The macro would paste a formula into the corresponding cells directly
to the right of it. (In this case, it would be cell B1)
2. In cell B1, the cell would show a value of "5", and would show the
following formula: =(50+200+250)/100


It is simple to show the formula itself with the range.formula method.

To replace the cell references with their values becomes more complex, as you need to write a routine that differentiates cell references from other contents. This becomes even more complicated if you also want to include NAMEs.

Furthermore, you write you also want to convert "formulas" to values. Does this refer to the entire formula? or also to various formulas that might be within the cell.
  #4   Report Post  
Old May 30th 12, 04:43 PM
Junior Member
 
First recorded activity by ExcelBanter: May 2012
Posts: 3
Default

Ron, thank you for the response. I was looking for what you were eluding to in your last question. Where all formulas, even within referenced cells, would be converted to values. Again, thanks for chiming in and for the help.



Quote:
Originally Posted by Ron Rosenfeld[_2_] View Post
On Tue, 29 May 2012 17:23:23 +0000, plotzmoney wrote:


Hello all, I was wondering if someone could please help me with a macro.
Any and all help is appreciated:

Essentially, it would be a macro where you take a cell (which has
various formulas and cell references in it) and convert those formulas
and references into their values within the formula bar into a separate
cell next to it.

For example:

Where Cell A1 = Cells (F2+F3+F4)/F5
F2 = 50
F3= 200
F4= 250
F5=100

Steps for Macro

1. I would highlight cell A1 (or if multiple cells, I would highlight
all of them)
2. I would run the macro

Macro Output

1. The macro would paste a formula into the corresponding cells directly
to the right of it. (In this case, it would be cell B1)
2. In cell B1, the cell would show a value of "5", and would show the
following formula: =(50+200+250)/100


It is simple to show the formula itself with the range.formula method.

To replace the cell references with their values becomes more complex, as you need to write a routine that differentiates cell references from other contents. This becomes even more complicated if you also want to include NAMEs.

Furthermore, you write you also want to convert "formulas" to values. Does this refer to the entire formula? or also to various formulas that might be within the cell.
  #5   Report Post  
Old May 31st 12, 03:15 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2010
Posts: 1,045
Default Display formulae as values of each cell reference

On Wed, 30 May 2012 15:43:00 +0000, plotzmoney wrote:


Ron, thank you for the response. I was looking for what you were eluding
to in your last question. Where all formulas, even within referenced
cells, would be converted to values. Again, thanks for chiming in and
for the help.



This User Defined Function can do some of what you want. It will only convert cell references to the values within those cells. I believe it will retain the format of those cells. Multicell range references will viewed as an array of values. Perhaps it will do what you require.

The long string of .pattern is a regular expression that should be able to recognize cell references of the A1 system. Ensure it is all on one line as the NG display may word wrap inappropriately.

I can't do much tweaking, but it should work for simple formulas. Anything more complex will require a formula parser, which is beyond what I can provide for you.


To enter this User Defined Function (UDF), <alt-F11 opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this User Defined Function (UDF), enter a formula like

=ConvertFormula(A1)

in some cell.

========================================
Option Explicit
Function ConvertFormula(r As Range) As String
Dim re As Object, mc As Object, m As Object
Dim re2 As Object, sRepl As String
Dim s As String, v As Variant
Dim i As Long
Dim c As Range
s = r.Formula

Set re = CreateObject("vbscript.regexp")
Set re2 = CreateObject("vbscript.regexp")

With re
.Global = True
.Pattern = "\$?\b(?:XF[A-D]|X[A-E][A-Z]|[A-W][A-Z]{2}|[A-Z]{2}|[A-Z])\$?(?:104857[0-6]|10485[0-6]\d|1048[0-4]\d{2}|104[0-7]\d{3}|10[0-3]\d{4}|[1-9]\d{1,5}|[1-9])d?\b([:\s]\$?(?:\bXF[A-D]|X[A-E][A-Z]|[A-W][A-Z]{2}|[A-Z]{2}|[A-Z])\$?(?:104857[0-6]|10485[0-6]\d|1048[0-4]\d{2}|104[0-7]\d{3}|10[0-3]\d{4}|[1-9]\d{1,5}|[1-9])d?\b)?"
End With

If re.Test(s) = True Then
Set mc = re.Execute(s)
For Each m In mc
If InStr(m, ":") 0 Then
ReDim v(1 To Range(m).Count)
For i = 1 To UBound(v)
v(i) = Range(m)(i)
Next i
sRepl = "{" & Join(v, ", ") & "}"
Else
sRepl = Range(m).Text
End If
With re2
.Global = True
.Pattern = "\b" & m & "\b"
s = .Replace(s, sRepl)
End With
Next m
End If

ConvertFormula = s

End Function
=================================================


  #6   Report Post  
Old May 31st 12, 04:48 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2010
Posts: 1,045
Default Display formulae as values of each cell reference

On Wed, 30 May 2012 15:43:00 +0000, plotzmoney wrote:

Ron, thank you for the response. I was looking for what you were eluding
to in your last question. Where all formulas, even within referenced
cells, would be converted to values. Again, thanks for chiming in and
for the help.


To have this run as a macro, on the range you select, as you specified in your original post, try this macro instead:

===================================
Option Explicit
Sub ConvertFormula()
Dim re As Object, mc As Object, m As Object
Dim re2 As Object, sRepl As String
Dim s As String, v As Variant
Dim i As Long
Dim c As Range, r As Range

For Each r In Selection

s = r.Formula

Set re = CreateObject("vbscript.regexp")
Set re2 = CreateObject("vbscript.regexp")

With re
.Global = True
.Pattern = "\$?\b(?:XF[A-D]|X[A-E][A-Z]|[A-W][A-Z]{2}|[A-Z]{2}|[A-Z])\$?(?:104857[0-6]|10485[0-6]\d|1048[0-4]\d{2}|104[0-7]\d{3}|10[0-3]\d{4}|[1-9]\d{1,5}|[1-9])d?\b([:\s]\$?(?:\bXF[A-D]|X[A-E][A-Z]|[A-W][A-Z]{2}|[A-Z]{2}|[A-Z])\$?(?:104857[0-6]|10485[0-6]\d|1048[0-4]\d{2}|104[0-7]\d{3}|10[0-3]\d{4}|[1-9]\d{1,5}|[1-9])d?\b)?"
End With

If re.Test(s) = True Then
Set mc = re.Execute(s)
For Each m In mc
If InStr(m, ":") 0 Then
ReDim v(1 To Range(m).Count)
For i = 1 To UBound(v)
v(i) = Range(m)(i)
Next i
sRepl = "{" & Join(v, ", ") & "}"
Else
sRepl = Range(m).Text
End If
With re2
.Global = True
.Pattern = "\b" & m & "\b"
s = .Replace(s, sRepl)
End With
Next m
End If

r.Offset(columnoffset:=1).Value = r.Text & s
Next r
End Sub
====================================
  #7   Report Post  
Old May 31st 12, 04:19 PM
Junior Member
 
First recorded activity by ExcelBanter: May 2012
Posts: 3
Default

Thanks so much Ron!!! Works great

Quote:
Originally Posted by Ron Rosenfeld[_2_] View Post
On Wed, 30 May 2012 15:43:00 +0000, plotzmoney wrote:

Ron, thank you for the response. I was looking for what you were eluding
to in your last question. Where all formulas, even within referenced
cells, would be converted to values. Again, thanks for chiming in and
for the help.


To have this run as a macro, on the range you select, as you specified in your original post, try this macro instead:

===================================
Option Explicit
Sub ConvertFormula()
Dim re As Object, mc As Object, m As Object
Dim re2 As Object, sRepl As String
Dim s As String, v As Variant
Dim i As Long
Dim c As Range, r As Range

For Each r In Selection

s = r.Formula

Set re = CreateObject("vbscript.regexp")
Set re2 = CreateObject("vbscript.regexp")

With re
.Global = True
.Pattern = "\$?\b(?:XF[A-D]|X[A-E][A-Z]|[A-W][A-Z]{2}|[A-Z]{2}|[A-Z])\$?(?:104857[0-6]|10485[0-6]\d|1048[0-4]\d{2}|104[0-7]\d{3}|10[0-3]\d{4}|[1-9]\d{1,5}|[1-9])d?\b([:\s]\$?(?:\bXF[A-D]|X[A-E][A-Z]|[A-W][A-Z]{2}|[A-Z]{2}|[A-Z])\$?(?:104857[0-6]|10485[0-6]\d|1048[0-4]\d{2}|104[0-7]\d{3}|10[0-3]\d{4}|[1-9]\d{1,5}|[1-9])d?\b)?"
End With

If re.Test(s) = True Then
Set mc = re.Execute(s)
For Each m In mc
If InStr(m, ":") 0 Then
ReDim v(1 To Range(m).Count)
For i = 1 To UBound(v)
v(i) = Range(m)(i)
Next i
sRepl = "{" & Join(v, ", ") & "}"
Else
sRepl = Range(m).Text
End If
With re2
.Global = True
.Pattern = "\b" & m & "\b"
s = .Replace(s, sRepl)
End With
Next m
End If

r.Offset(columnoffset:=1).Value = r.Text & s
Next r
End Sub
====================================
  #8   Report Post  
Old May 31st 12, 11:13 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2010
Posts: 1,045
Default Display formulae as values of each cell reference

On Thu, 31 May 2012 15:19:39 +0000, plotzmoney wrote:

Thanks so much Ron!!! Works great


Glad to help. Thanks for the feedback.
  #9   Report Post  
Old January 15th 20, 10:27 AM
Junior Member
 
First recorded activity by ExcelBanter: Jan 2020
Posts: 3
Default

This is great. Is there any way to keep the formatting of the referenced cells?

eg:
A1; 2.11237864872634 (formatted to 2 DP, for example)
A2: 3.141592654 (formatted to 2 DP, for example)
A3: = A1 + A2
ConvertFormula(A3) shows "2.11237864872634 + 3.15297459"

Instead I would like "2.11 + 3.14"

Any help would be greatly appreciated!


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I use formulae to display a specific picture in a cell? Tricia Excel Discussion (Misc queries) 1 June 19th 08 10:50 PM
Display formulae instead of showing values in cells Kent Excel Worksheet Functions 3 November 6th 07 04:27 AM
Remote cell reference in formulae Graham Excel Discussion (Misc queries) 3 July 25th 07 01:32 PM
How do I get just 1 cell to display it's formulae not whole sheet? MrsWiz Excel Worksheet Functions 5 August 2nd 06 07:05 PM
Shortcut key to display change the display from displaying cell values to cell formulae Adrian[_7_] Excel Programming 3 September 14th 04 12:07 PM


All times are GMT +1. The time now is 09:33 PM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017