formulas to text

[jnich]

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

 

[BrianB]

Have a look at the ConvertFormula Method in VB editor help. This might do what you want.