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?
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?
~Anne Troy
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 (variables changes )
Last edited by Zack Barresse; 06-17-2004 at 08:34 AM. Reason: Tags now fixed...
Regards, Zack Barresse
Check out the KB! :|: BOARD TAGS: WHAT ARE THEY AND HOW DO I USE THEM
What is a Microsoft MVP? | Free Microsoft Courses | My Book on Excel Tables
Okay. I've removed the VBA tags on it, at least until that's fixed.
Let's find out if it helps?
~Anne Troy
So are you also looking for error checking here Anne?
Regards, Zack Barresse
Check out the KB! :|: BOARD TAGS: WHAT ARE THEY AND HOW DO I USE THEM
What is a Microsoft MVP? | Free Microsoft Courses | My Book on Excel Tables
Nope. Just a simple macro, methinks. Hang tight.
~Anne Troy
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
You cheat.Originally Posted by Dreamboat
You could do it with a nice UDF too:
Function ShowFormula(R As Range) ShowFormula = R.Formula End Function
"Computers are useless. They can only give you answers." - Pablo Picasso
Mark Rowlinson FIA | The Code Net
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
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
Might also want to consider autofitting the columns and/or wrapping text etc. - the formulae could well be quite long.
"Computers are useless. They can only give you answers." - Pablo Picasso
Mark Rowlinson FIA | The Code Net
I'm going to continue to cheat as long as I think others might ask the same question, Ab.You cheat.
I want our kb to be *definitive*.
~Anne Troy
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.
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
Last edited by roos01; 06-18-2004 at 07:19 AM.
Hey, Jeroen.
See my signature for how to do the VBA code properly.
You can edit your post.
~Anne Troy
Hi Jeroen,Originally Posted by roos01
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.
Fastest way: download MOREFUNC (http://longre.free.fr/english/index.html)Originally Posted by Dreamboat
and use the formula
I use it daily!!=FORMULATEXT(C1)
Software: LibreOffice 3.3 on Mac OS X 10.6.5
(retired Excel 2003 user, 3.28.2008 )
Humanware: Older than dirt
--------------------
old, slow, and confused
but at least I'm inconsistent!
Rich
Hi Shades,
Very nice!
We could do the same thing with a UDF:
Function FTEXT(f As Range) FTEXT = f.Formula End Function
Regards, Zack Barresse
Check out the KB! :|: BOARD TAGS: WHAT ARE THEY AND HOW DO I USE THEM
What is a Microsoft MVP? | Free Microsoft Courses | My Book on Excel Tables
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
Hi Jeroen,
Very nice! The only thing I'd change is dim the 'cell':
Just for good practice. But it works like a charm, very nice!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
Regards, Zack Barresse
Check out the KB! :|: BOARD TAGS: WHAT ARE THEY AND HOW DO I USE THEM
What is a Microsoft MVP? | Free Microsoft Courses | My Book on Excel Tables
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