Macro button

[adavies]

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

 

[ryal001]

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]

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]

What did you mean write it down or record? I haven't used the record macro button before

 

[ryal001]

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:

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:

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]

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

 

[adavies]

Thanks all I'll try it and see