Inhibit auto-calculate for *some* cells
Posted by Bengt J on September 09, 2001 6:34 AM
Hi,
I would like to freeze some values, and still be able to recalculate some other cell values.
The actual problem is this: I tried to make a multiplication exercise for my kids, so I wanted the factors to be generated with the RAND function. Then the kids would fill in the answers, and pressing F9 for manual recalculation would fill in some "Bravo!" or "Sorry, error." according to their answers.
However, F9 also recalculates the random numbers, thus losing the old exercise. The kids' answers are still there, but now compared to a new exercise! Of course, this is almost always wrong.
Any suggestion, anyone? Do I have to learn macros and create one "New exercises"-button and one "Check my answers"-button? There must be a simpler way...
/Bengt J
Posted by Tom Urtis on September 09, 2001 8:04 PM
Here's one suggestion among several options
Because the RAND function is volatile, you might consider freezing the values by Copy > Paste Special Value over the RAND input range. Paste this code into a module and assign it to a button on the worksheet. Remember to adjust for the actual range, and delete the "Calculate" line if the calculation is to be done after the users need to first look at and consider the random numbers.
Others may have better ideas, but this might get you going for now.
Sub Random()
Application.ScreenUpdating = False
Range("A1:D10").Select
Selection.ClearContents
Range("A1").Select
ActiveCell.FormulaR1C1 = "=RAND()"
Selection.AutoFill Destination:=Range("A1:A10"), Type:=xlFillDefault
Range("A1:A10").Select
Selection.AutoFill Destination:=Range("A1:D10"), Type:=xlFillDefault
Range("A1:D10").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
Range("A1").Select
Calculate
Application.ScreenUpdating = True
End Sub
HTH
Tom Urtis
