PDA

View Full Version : Solved: Show a Formula in Another Cell



Anne Troy
06-16-2004, 11:17 PM
Suppose cell C1 says =A2/B2 and the resulting value is, say, 150.

Suppose in D1, I want to see the formula in C1 AS TEXT
Suppose that I want to do this for TOO many cells for me to retype with an apostrophe in front of them.

Any ideas anybody?

Zack Barresse
06-16-2004, 11:34 PM
Hi Anne,

Here's the basics:

Sub convert()

Dim x As Long
Dim lRow As Long
lRow = Range("C65536").End(xlUp).Row
For x = 1 To lRow
If Range("C" & x).HasFormula Then
Range("D" & x).Formula = "'" & Range("C" & x).Formula
End If
Next

End Sub


The only thing this will not do, is if the formula happens to point to the cooresponding cell in column D, column C cell will get the #VALUE error. If not, then this'll work. Haven't done any error handling for that issue yet. Thought I'd see what you had to say.

EDIT: That's not a note on the right, it's supposed to be a single quote inside double quotes. Mark's VBA tags are automatically putting a space in there :roll: (variables changes :) )

Anne Troy
06-16-2004, 11:41 PM
Okay. I've removed the VBA tags on it, at least until that's fixed. :)
Let's find out if it helps?

Zack Barresse
06-16-2004, 11:44 PM
So are you also looking for error checking here Anne?

Anne Troy
06-16-2004, 11:53 PM
Nope. Just a simple macro, methinks. Hang tight. :)

brettdj
06-17-2004, 03:45 AM
Hi Zack,

You might want to dimension x & lrow as Long rather than Integer in case there are more than 32,768 rows

Cheers

Dave

SJ McAbney
06-17-2004, 04:39 AM
Suppose cell C1 says =A2/B2 and the resulting value is, say, 150.

Suppose in D1, I want to see the formula in C1 AS TEXT
Suppose that I want to do this for TOO many cells for me to retype with an apostrophe in front of them.

Any ideas anybody?
You cheat. ;)

mark007
06-17-2004, 05:02 AM
You could do it with a nice UDF too:

Function ShowFormula(R As Range)
ShowFormula = R.Formula
End Function

:)

Richie(UK)
06-17-2004, 05:26 AM
Incorporating the simple UDF idea with a user-interface (to select the range to work with and the range to post to) we could use:
Sub FTT()
'Converts the formulas in a range to the text of the formula

Dim rngFrom As Range, rngSC As Range, rngTo As Range, rngCell As Range
Dim lStartRow As Long, lSCRow As Long, iStartCol As Integer, iSCCol As Integer
Dim lCalc As Long

Const MessageFrom As String = "Please select the range containing the formulas:"
Const MessageTo As String = "Please select the first cell in the range to copy to:"
Const Title As String = "Formula To Text"
Const DefaultFrom As String = "Select a range"
Const DefaultTo As String = "Select a cell"

On Error Resume Next
Set rngFrom = Application.InputBox(MessageFrom, Title, DefaultFrom, , , , , 8)
Set rngSC = rngFrom.SpecialCells(xlCellTypeFormulas)
If rngSC Is Nothing Then Exit Sub
Set rngFrom = Application.Intersect(rngFrom, rngSC)
On Error GoTo 0
If rngFrom Is Nothing Then Exit Sub
'get the 'from' ranges
'use Intersect to avoid ALL SCs being used with single cell selection

With rngFrom
lStartRow = .Rows(1).Row
iStartCol = .Columns(1).Column
End With

Do
On Error Resume Next
Set rngTo = Application.InputBox(MessageTo, Title, DefaultTo, , , , , 8)
On Error GoTo 0
If rngTo Is Nothing Then
Set rngFrom = Nothing
Exit Sub
End If
Loop Until rngTo.Count = 1
Set rngTo = rngTo.Resize(rngFrom.Rows.Count, rngFrom.Columns.Count)
'get the 'to' range
'gets a single cell and then resizes to match the 'from' range

With Application
lCalc = .Calculation
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
For Each rngCell In rngFrom
With rngCell
lSCRow = .Row
iSCCol = .Column
End With
rngTo.Item((lSCRow - lStartRow) + 1, (iSCCol - iStartCol) + 1).Value = _
"'" & FormulaToText(rngCell)
Next
'use UDF to process each cell (convert formula to text)

Set rngFrom = Nothing: Set rngSC = Nothing
Set rngTo = Nothing: Set rngCell = Nothing
With Application
If Not lCalc = 0 Then .Calculation = lCalc
'in case lCalc not set before error occurs
.ScreenUpdating = True
End With

End Sub

Function FormulaToText(ByRef rng As Range) As String
'convert the formula in a range into its text representation

With rng
If Not .Count > 1 Then
If .HasFormula Then FormulaToText = .Formula
If .HasArray Then FormulaToText = "{" & .Formula & "}"
End If
End With

End Function

brettdj
06-17-2004, 05:58 AM
Nice one Richie

I mucked around for a while trying to avoid looping through the formula range to do this conversion but no luck as of yet

mark007
06-17-2004, 06:07 AM
Might also want to consider autofitting the columns and/or wrapping text etc. - the formulae could well be quite long.

:)

Anne Troy
06-17-2004, 07:52 AM
You cheat. ;)
I'm going to continue to cheat as long as I think others might ask the same question, Ab. ;)

I want our kb to be *definitive*.

:)

roos01
06-18-2004, 07:06 AM
Hello,

if you just want to see the formulas mentioned in column C in Column D you might think about using next macro. this puts the formula of your selected range in the column to the right of it.

VBA:

Sub ShowFormula()
Application.ScreenUpdating = False
For Each cell In Selection
cell.Offset(0, 1).Formula = "'" & cell.Formula
Next cell
Application.ScreenUpdating = True
End Sub


regards,
Jeroen

Anne Troy
06-18-2004, 07:10 AM
Hey, Jeroen. :)

See my signature for how to do the VBA code properly. :)
You can edit your post.

Richie(UK)
06-18-2004, 08:42 AM
Hello,

if you just want to see the formulas mentioned in column C in Column D you might think about using next macro. this puts the formula of your selected range in the column to the right of it.

VBA:

Sub ShowFormula()
Application.ScreenUpdating = False
For Each cell In Selection
cell.Offset(0, 1).Formula = "'" & cell.Formula
Next cell
Application.ScreenUpdating = True
End Sub


regards,
Jeroen

Hi Jeroen,

Thanks for your input.

The trouble with this approach is that it will act on all cells in the selection rather than just those that contain fomulas. So we end up with text copies of everything; formulas, numbers, text. That's why some of the earlier code examples use SpecialCells or HasFormula in them. ;)

shades
06-18-2004, 10:30 AM
Suppose cell C1 says =A2/B2 and the resulting value is, say, 150.

Suppose in D1, I want to see the formula in C1 AS TEXT
Suppose that I want to do this for TOO many cells for me to retype with an apostrophe in front of them.

Any ideas anybody?Fastest way: download MOREFUNC (http://longre.free.fr/english/index.html) (http://longre.free.fr/english/index.html))
and use the formula

=FORMULATEXT(C1)

I use it daily!!

Zack Barresse
06-18-2004, 10:48 AM
Hi Shades,

Very nice!

We could do the same thing with a UDF:

Function FTEXT(f As Range)
FTEXT = f.Formula
End Function

roos01
06-18-2004, 11:02 AM
Hello Richie,
your right it shows all the values as well as text as numbers etc. perhaps next approachs works,

it shows only the formula's in the cells if the selection cells contains formula's. Hope this would do the trick.


Sub ShowOnlyFormula()
Application.ScreenUpdating = False
For Each Cell In Selection
If Cell.HasFormula = True Then
Cell.Offset(0, 1).Formula = "'" & Cell.Formula
End If
Next Cell
Application.ScreenUpdating = True
End Sub


Jeroen

Zack Barresse
06-18-2004, 11:29 AM
Hi Jeroen,

Very nice! The only thing I'd change is dim the 'cell':

Option Explicit
Sub ShowOnlyFormula()
Dim cell As Range
Application.ScreenUpdating = False
For Each cell In Selection
If cell.HasFormula = True Then
cell.Offset(0, 1).Formula = "'" & cell.Formula
End If
Next cell
Application.ScreenUpdating = True
End Sub

Just for good practice. But it works like a charm, very nice!

roos01
06-18-2004, 11:46 AM
Hi Zack,

Thanks for the adjustments. I Still have to pay more attention on DIM's I think your post of using a function is fancier. it is shorter and simple.

here also another addaption to you function (added the check on formula)


Function FTEXT(f As Range)
If f.HasFormula = True Then
FTEXT = f.Formula
Else
FTEXT = ""
End If
End Function


regards,
Jeroen

Zack Barresse
06-18-2004, 11:50 AM
LOL! You're good!

I just made a post to the KB with this:

Function FTEXT(f As Range)
If f.HasFormula Then
FTEXT = f.Formula
Else: FTEXT = f
End If
End Function

I thought it was too bare-boned. Plus any numerical value would've come out as textual, and I didn't like that idea! But this seems to work ok! :)

Mike_R
06-18-2004, 12:38 PM
For a less invasive appraoch, how about something like the following:Sub ShowFormulas()
With ActiveWindow
.NewWindow
.DisplayFormulas = True
.Parent.Windows.Arrange ArrangeStyle:=xlVertical
End With
End Sub :),
Mike

Zack Barresse
06-19-2004, 12:16 PM
You know, I just realized that the UDF I posted was first posted by Mark007! (Sorry Mark) LOL!



You could do it with a nice UDF too:

Function ShowFormula(R As Range)
ShowFormula = R.Formula
End Function

:)

Ken Wright
06-19-2004, 01:29 PM
The other option would be to write a comment to every cell containing a formula, and then display the comment. This then gives you an easy option to switch on and off using view/comments. Gets a bit messy with lots of contiguous cells with formulas, but no more so than anything else.

Sub Formulas()
Dim cel As Range
On Error Resume Next
Application.ScreenUpdating = False
For Each cel In ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas, 23)
With cel
.AddComment
.Comment.Visible = True
.Comment.Text Text:=.Formula
End With
Next cel
Application.ScreenUpdating = True
End Sub


Regards
Ken.....................

Zack Barresse
06-19-2004, 02:07 PM
Nice one Ken!

Here is an addition to your code that will resize the comment box also:

Sub Formulas()
Dim cel As Range
Dim com As String
On Error Resume Next
Application.ScreenUpdating = False
For Each cel In ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas, 23)
With cel
If cel.Comment.Text = "" Then
.AddComment
.Comment.Visible = True
.Comment.Text Text:=.Formula
.Comment.Shape.ScaleHeight 0.27, msoFalse, msoScaleFromTopLeft
End If
End With
Next cel
Application.ScreenUpdating = True
End Sub


The only thing this really won't do, is adjust if you already have a comment in a cell with a formula. Also if you have formulas in consecutive columns (on the same row) as the comment fields will most likely overlap each other.

tommy bak
06-23-2004, 05:08 AM
Well, my code actually looks a lot like Zack's code, but I'll post it anyway.
For non-us users it's pracical to have the comment in their language.
This code will change all comments in selection (if any)
I think the only difference is the use of FormulaLocal and AutoSize.


Sub show_formula_in_comment()
Dim rCell As Range, OldText As String
Application.ScreenUpdating = False
On Error Resume Next
For Each rCell In Selection.SpecialCells(xlCellTypeFormulas, 23)
With rCell
.AddComment
With .Comment
.Text Text:=CStr(rCell.FormulaLocal)
.Shape.TextFrame.AutoSize = True
.Visible = True
End With
End With
Next
Application.ScreenUpdating = True
End Sub

'Sub to remove the comments
Sub RemoveComments()
Selection.ClearComments
End Sub


Tommy Bak

Scottie P
06-23-2004, 07:27 AM
Hello Tommy B!

Nice to see you here :)

Scott (a.k.a. ViperGTS)

tommy bak
06-23-2004, 08:00 AM
Thanks, Scott.
Just found this place today (thanks to shades :yes )and it really looks nice.

regards
Tommy Bak

Insomniac
06-24-2004, 01:45 PM
Just another idea on a theme.
(pinched the .HasArray & .FormulaLocal from the previous posts)
Using DataValidation Input Message via WorksheetChange Event:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim c As Range, frm As String, but As String

Application.EnableEvents = False
On Error Resume Next
With Target
.SpecialCells(xlCellTypeBlanks).Validation.Delete 'remove for blanks
.SpecialCells(xlCellTypeConstants).Validation.Delete 'remove if not a formula any more
For Each c In .SpecialCells(xlCellTypeFormulas)
but = vbNullString
With c
frm = .FormulaLocal
If .HasArray Then frm = "{" & .FormulaLocal & "}"
If Len(frm) > 254 Then but = " [too long]"
frm = Left(frm, 254)

With .Validation
.Delete
.Add Type:=xlValidateInputOnly
.InputTitle = "Formula in " & c.Address & but
.InputMessage = frm
.ShowInput = True
End With
End With
Next
End With
Application.EnableEvents = True
End Sub

Positives:
1. Automated via Change Event
2. Can drag the message anywhere on the screen & it stays there (eg. under formula bar to veiw whilst editing).
3. If (God forbid) you use the Office Assistant it will display the formula in a larger font in its balloon.
4. Will work as soon as formula is entered, with Copy & Paste, drag&drop - no need to run a macro.

Negatives:
1. Deletes any validation you may have (could be modified to at least reapply all but Input message)
2. Formulas longer than 255 characters will be chopped.
3. Excel will crash in xl97 if you Copy & Paste or Drag & Drop an array formula to more than 1 cell.

pierrelogic
07-19-2004, 07:03 AM
just out of curiosity what does the "23" really stand for in the formulas above?

(i.e. Selection.SpecialCells(xlCellTypeFormulas, 23)

I understand it's a value but where is that value derived from?

Sorry for digging up an older post but doing a search does that. :rolleyes:

NateO
07-19-2004, 08:09 AM
Microsoft created it, it's a numeric constant in c-code, it's sitting on your hard drive (most likely). 23 refers to a combination all 4 value types (constants) of Special Cells -> Formulas. Those being:

xlErrors, xlLogical, xlNumbers, xlNumbers.

All of which are early bound references numeric, constant arguments. E.g., xlNumbers = 1.

23 is actually the sum of all four numeric constants:

Errors = 16
Logical = 4
Numbers = 1
Text = 2

There's only one argument to be passed. 23 is moot in this case as the argument is both optional and 23 is the value by default unless stated otherwise.

You may also want to refer to the SpecialCells Method in the VBE help file.