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
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,125
Messages
5,599,850
Members
414,342
Latest member
K Darrell Smith

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