formulas to text

jnich

New Member
Joined
Feb 6, 2004
Messages
1
Hi - I am trying to build a macro which first edits a formula - putting a "'" in front of it thereby changing it to text then copying it several cells up and formatting it to alignment,general,top,wrap, and merge with the two or so cells underneath (in case the formula is really long) - go back to the original formula (now text) and edit it to be back to original a formula - all this to show the reader an example of that columns formulas. The first run works but when I put the cursor on the next column over to do it to that one it brings in the previous columns formula, etc - I tried playing with the absolute reference button but could not get the result I was looking for.This is what works on the first run.
Sub test()
'
' test Macro
' Macro recorded 2/6/2004 by J. Nicholas Boyd
'
' Keyboard Shortcut: Ctrl+t
'
Range("F16").Select
ActiveCell.FormulaR1C1 = "'=+I16+B16+C16+D16"
Range("F16").Select
Selection.Copy
Range("F13").Select
ActiveSheet.Paste
Range("F16").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=+RC[3]+RC[-4]+RC[-3]+RC[-2]"
Range("F13").Select
ActiveCell.FormulaR1C1 = "'e.g. =+I16+B16+C16+D16"
Range("F13:F15").Select
With Selection
.VerticalAlignment = xlTop
.WrapText = True
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = True
End With
End Sub
giving:
e.g. =+I16+B16+C16+D16


233,554

next i attempt to change from absolute referencing:
test2 Macro
' Macro recorded 2/6/2004 by J. Nicholas Boyd
'
' Keyboard Shortcut: Ctrl+u
'
ActiveCell.Offset(3, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "'=+I16+B16+C16+D16"
ActiveCell.Select
Selection.Copy
ActiveCell.Offset(-3, 0).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(3, 0).Range("A1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=+RC[3]+RC[-4]+RC[-3]+RC[-2]"
ActiveCell.Offset(-3, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "'e.g. =+I16+B16+C16+D16"
ActiveCell.Range("A1:A3").Select
With Selection
.VerticalAlignment = xlTop
.WrapText = True
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = True
End With
ActiveCell.Offset(3, 0).Range("A1").Select
End Sub
As you can see it laid in previous edits (I used the F2 key)
It has been a long time since I did macro and it was with Lotus 123 - but I remember that the edit function and up,down,right, etc worked.

I cannot get this to work - can anyone help
Nick
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

BrianB

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
Have a look at the ConvertFormula Method in VB editor help. This might do what you want.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,037
Messages
5,856,966
Members
431,841
Latest member
jaybeem

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top