Modify values without creating a new column

Question

I would like to replace the value of a cell with the result of a formula which refers to the cell itself. I tried to allow circular reference (1 cycle) but it didn't help.

Say I have a number in cell A1 (and cells below), I would like to replace its content with the following formula: =A1/60 (the same for the rest of the column).

Of course I can do that with a new column on the side, but I would like to achieve this without inserting a new column. Or maybe create some automation that adds a new one far away, do stuff and then removes it.

possible duplicate of [Using a UDF in Excel to update the worksheet](http://stackoverflow.com/questions/23433096/using-a-udf-in-excel-to-update-the-worksheet) — , Jun 13 '14 at 11:57
Have a look at [**this**](http://stackoverflow.com/questions/6335563/self-reference-for-cell-column-and-row-in-worksheet-functions) SO question, it may help — Alex Gidan, Jun 13 '14 at 11:58

score 3 · Answer 1 · edited Jun 14 '14 at 13:46

3

Enter 60 in a cell somewhere and copy it.
Select ColumnA ..... FILE .... Clipboard ..... Paste - Paste Special
Then check Divide, press OK

edited Jun 14 '14 at 13:46

brettdj

54,857
16
114
177

answered Jun 13 '14 at 12:36

pnuts

58,317
11
87
139

who knows, it's likely to be a peril of editing/commenting elsewhere. Its the best answer here. – brettdj Sep 19 '15 at 05:58

Gary's Student · Accepted Answer · 2014-06-13T12:40:39.723

Select the cells you want to change and run this small macro:

Sub FormulaMaker()
    Dim V As Variant
    Dim r As Range
    For Each r In Selection
        V = r.Value
        If V = "" Then V = 0
        r.Formula = "=" & V & "/60"
    Next r
End Sub

Macros are very easy to install and use:

ALT-F11 brings up the VBE window
ALT-I ALT-M opens a fresh module
paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the macro:

bring up the VBE window as above
clear the code out
close the VBE window

To use the macro from Excel:

ALT-F8
Select the macro
Touch RUN

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

Macros must be enabled for this to work!

Modify values without creating a new column

2 Answers2

Linked