Recorded Macro used to work great, but now??
Recorded Macro used to work great, but now??
(OP)
This spreadsheet started out as a Quattro Pro (DOS) sheet and I brought it in to Excel some time ago. Recently, I discovered a quirk while editing a formula. In Excel 2007, I went to Options> Advanced > Lotus Compatibility Options and unchecked a couple of options called Transition Formula Evaluation and Transition Formula Entry. Then I used "Apply Names" to make all of the formulas work properly.
Originally, I used Excel 2000 to record the macro. Since I made the changes noted above, the constraints stored in the range named "OPT" don't seem to work with named cells like "Variable" but do work with "$B$80". So, I changed those refences.
But now after the macro runs, the current cell selected is now the cell holding the solver solution. Before, the macro used to run without moving the current cell selection. This is driving me crazy. Here is my simple code. I assigned the macro to a control button as well as the shortcut below.
Sub Maximum_Moment()
'
' Maximum_Moment Macro
' Macro recorded 10/1/2007 by Steve Gregory For Maximum moment or using the solver
'
' Keyboard Shortcut: Ctrl+m
'
SolverLoad loadArea:=Range("OPT")
SolverOptions Iterations:=200
SolverSolve UserFinish:=True
End Sub
Originally, I used Excel 2000 to record the macro. Since I made the changes noted above, the constraints stored in the range named "OPT" don't seem to work with named cells like "Variable" but do work with "$B$80". So, I changed those refences.
But now after the macro runs, the current cell selected is now the cell holding the solver solution. Before, the macro used to run without moving the current cell selection. This is driving me crazy. Here is my simple code. I assigned the macro to a control button as well as the shortcut below.
Sub Maximum_Moment()
'
' Maximum_Moment Macro
' Macro recorded 10/1/2007 by Steve Gregory For Maximum moment or using the solver
'
' Keyboard Shortcut: Ctrl+m
'
SolverLoad loadArea:=Range("OPT")
SolverOptions Iterations:=200
SolverSolve UserFinish:=True
End Sub





RE: Recorded Macro used to work great, but now??
TTFN
FAQ731-376: Eng-Tips.com Forum Policies
Chinese prisoner wins Nobel Peace Prize
RE: Recorded Macro used to work great, but now??
Another macro does a similar thing with the currently selected cell. It runs from the "Find Max Moment, X for Wheel Loads".
RE: Recorded Macro used to work great, but now??
1) Tools>>Options>> Select Solver Add-in. Afterwards, you will see the Solver Coomand under the Tools Menu. And you will need to select Solver. Otherwise, it will still not work. Close Solver.
2) Open Visual Basic Editor. Tools >> Reference>> Select Solver. There might be another solver.xlm. Unselect that.
I don't think there is anything to do with named ranges. Anyways, I attached the file that I changed.
Also, can you explain what P1C, W1C, EMOM, TRAP, LOP, RCP represents? Can I use the spreadsheet?
RE: Recorded Macro used to work great, but now??
My current dilema is that after the macro runs, the current cell selected is now the cell holding the solver solution. Before, the macro used to run without moving the current cell selection. This is driving me crazy.
RE: Recorded Macro used to work great, but now??
RE: Recorded Macro used to work great, but now??
Range("m20").value = ActiveCell.Address
...
..
ActiveCell.FormulaR1C1 = m20
p.s. Using this spreadsheet might be hazardess to your career. There are some nasty bugs that are difficult to find and correct. That was part of the reason that I was working on it in my spare time. Most of the time, the analysis is correct, but once in a while the answers are not right and it is not very obvious that they are incorrect.
RE: Recorded Macro used to work great, but now??
x = activecell.address
...
...
range(x).select
This will work.
RE: Recorded Macro used to work great, but now??
One more thing, It makes me a little dizzy, so I tried to turn off the screen movements at the beginning and end of the macro with:
Application.ScreenUpdating = False
.
.
Application.ScreenUpdating = True
The activecell was correct but it was not visible because the screen was scrolled down to row 80. How do I keep the original screen location?
RE: Recorded Macro used to work great, but now??
Application.ScreenUpdating = False
x = activecell.address
.
.
Application.ScreenUpdating = True
range(x).select
This gets you back to the activecell. It still gets flickering but less than before and about the same when running the max moment with wheel load. I don't why there are still flicker even with the updating disabled and why it does not return the activecell with the way you set it up.
By the way, 2 questions about the max function and count function in the range ("OPT"). Is the max function necessary if the argument is only cell A80 and count function necessary if the argument is only cell B80?
RE: Recorded Macro used to work great, but now??
Thanks for your help. I am still not sure what exactly caused my macro routine to stop working.
RE: Recorded Macro used to work great, but now??
CODE
'
' Module-wide variables to record the screen state etc.
'
Dim OldRow As Long, OldCol As Long, OldSheet As String
Dim OldCalcState, OldUpdateState, OldCell
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Sub RecordScreenState()
'
' Records various aspects of the screen state so that the state
' can later be restored (by Sub RestoreScreenState), then
' temporarily changes some things for quicker processing.
'
OldSheet = ActiveSheet.Name
OldRow = ActiveWindow.ScrollRow
OldCol = ActiveWindow.ScrollColumn
Set OldCell = ActiveCell
OldUpdateState = Application.ScreenUpdating
OldCalcState = Application.Calculation
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
'
End Sub
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Sub RestoreScreenState()
'
' Restore the screen state to as it was when it was recorded
' by Sub RecordSceeenState().
'
Application.ScreenUpdating = True
' ScreenUpdating should have been set back to OldUpdateState but
' on rare occasions (due to untrapped erros of some sort) it
' could end up locked into False.
Application.Calculation = OldCalcState
Worksheets(OldSheet).Select '******** ADDED IN NOVEMBER 2009 *******
ActiveWindow.ScrollRow = OldRow
ActiveWindow.ScrollColumn = OldCol
On Error Resume Next
OldCell.Activate
On Error GoTo 0
'
End Sub