Macro button

adavies

Board Regular
Joined
Nov 15, 2005
Messages
61
Is it possible to make a macro button that will reset formulas to their original format?

By that I mean when you sort data the formulas get moved around... is it possible to make them revert back?

And how?...please and thank you
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

ryal001

Board Regular
Joined
Feb 8, 2006
Messages
64
If I understand your question correctly, you have a column or columns with formulas based on other cells in the same row. You sort the rows based on criteria in other cells and, as a result of the way you do this and/or the nature of the formulas (absolute versus relative referencing), the formulas no longer refer to cells on the same row.

In this case, you ought to be able to insert the correct formula into the first row and then copy down the formula to all the relevant rows. It would be straight forward to record such a macro or to write it yourself and attach it to a button.

It will be more complicated if for some reason the formauls don't copy down correctly becuase of their structure. If this is the case you should paste your formula so we can see why this would be.
 

adavies

Board Regular
Joined
Nov 15, 2005
Messages
61
In this case, you ought to be able to insert the correct formula into the first row and then copy down the formula to all the relevant rows.

I am able to do the above, but it would make my job easier to just have a button for the people that use it but don't understand how to correct the formulas...
 

adavies

Board Regular
Joined
Nov 15, 2005
Messages
61
What did you mean write it down or record? I haven't used the record macro button before
 

ryal001

Board Regular
Joined
Feb 8, 2006
Messages
64
From the Excel menu, select Tools > Macro > Record new macro.

A dialog box name "Record Macro" will open with "Macro name" highlighted. Call it anything you like (e.g. Correct_Formulas) but don't use spaces or special characters. Make sure "Store macro in" is set to "This Workbook". Put in a description of what the macro does.

Click OK and the dialog will dissapear and a small menu bar will open with a little square blue button; this is the "Stop" button. Excel is now recording everything you do (well almost everything) until you click the stop button.

Insert your cursor at the top of the relevant column and type in the correct formula. Use your mouse to copy down the formula to all the required rows so that the formula increments correctly. Then click the stop recordig button.

On the Excel menu, click View > Toolbars > Forms.

On the Forms toolbar click the Button icon (should be fourth button down - if you hover your cursor over it a tool tip will say "Button") and your cursor will turn into a cross-hair. Move your cursor to the place on the worksheet you want to put your button, hold down the left mouse button and drag out to create the button.

An "Assign Macro" dialog will open. The macro you created above should be in the list of available macros (if it's not, make sure you select "This Workbook" in the "Macros in" drop down box). Select your macro and click OK.

You now have an issue in that if you add rows to your worksheet the macro will not automatically update and not all the formulas will be fixed. To cover this you could name the range covering the cells in the relevant column (do you know how to do this?) and then edit the macro and use the name. Then in the worksheet, make sure any new rows are added inside the named range. To edit the macro, right-click the button you made above, select "Assign Macro" and highlight your macro then click "Edit". You should see code similar to this:

Code:
Sub CorrectFormula()
'
' CorrectFormula Macro
' Macro recorded 23/07/2007 by Rob Yallop
'

'
    Range("G1").Select
    ActiveCell.FormulaR1C1 = "=RC[-4]+RC[-3]"
    Range("G1").Select
    Selection.AutoFill Destination:=Range("G1:G11"), Type:=xlFillDefault
    Range("G1:G11").Select
    Range("G1").Select
End Sub

Now change the cell reference to something like:

Code:
Sub CorrectFormula()
'
' CorrectFormula Macro
' Macro recorded 23/07/2007 by Rob Yallop
'

'
    Range("G1") = "=C1+D1"
    Range("G1").Select
    Selection.AutoFill Destination:=Range("MyRangeName"), Type:=xlFillDefault
    Range("G1").Select
End Sub

Save the code then return to Excel and test the button.
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
Take a look at INDIRECT in the helpfile. That'll help you overcome the sorting issue.

Note that you might want to post your formula, as INDIRECT can get kinda quirky with range references.

Hope that helps,

Smitty

(Rob, check out www.mgear.com, they've got some killer Atomic deals right now...Too bad my wife won't let me get another pair... :groan:)
 

Forum statistics

Threads
1,181,649
Messages
5,931,217
Members
436,784
Latest member
amuljono

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