Managing Random Numbers

[omagoodness]

Good evening. I created a small Cross Number puzzle for my granddaughter to practice her math skills. The puzzle resembles a crossword puzzle using mathematical equations. Two numbers in the equation are filled in and the player must enter the missing number to satisfy the equation. The two given numbers are randomly generated using

=RANDBETWEEN(1,99)

. My question is this, how can I stop Excel from recalculating the random numbers everytime a cell is changed? Ideally, I will get a new set of random numbers each time I open the file (or manually refresh the sheet) but not whenever another cell is changed. Sample puzzle is below.

 

[bebo021999]

With data like this:

Try below code

Option Explicit
Sub clear()
If MsgBox("All cells will be cleared!", vbYesNo) = vbNo Then Exit Sub
Dim cell As Range
For Each cell In Range("A2:F8")
    If cell.Value <> "=" And cell.Value <> "+" Then cell.ClearContents
Next
End Sub
Sub randR()
If MsgBox("All cells will be randomize again!", vbYesNo) = vbNo Then Exit Sub
clear
Dim i&, ar
ar = Array("D2", "A5", "B4")
Randomize
For i = 0 To UBound(ar)
    Range(ar(i)).Value = Int(Rnd * 99) + 1
Next
Range("F2").Value = Int(Rnd * (99 - Range("D2").Value) + Range("D2").Value)
Range("D6").Value = Int(Rnd * (99 - Range("D2").Value) + Range("D2").Value)
Range("E5").Value = Int(Rnd * (99 - Range("A5").Value) + Range("A5").Value)
Range("B8").Value = Int(Rnd * (99 - Range("B4").Value) + Range("B4").Value)
Range("D8").Value = Int(Rnd * (99 - Range("B8").Value)) + 1
End Sub

puzzle.xlsm

docs.google.com

 

[smozgur]

Unfortunately, you can't stop the RAND and RANDBETWEEN functions recalculating unless setting manual calculation.

However, you can use still keep the formulas and calculate the random values and the following VBA code in ThisWorkbook class module will convert all formulas to values on file opening.
You can ideally create a master workbook or template to create a new workbook each time you'd like to start with new random values.

Private Sub Workbook_Open()
Dim rng As Range

    Set rng = Sheet1.Range("A2:F8") ' Adjust the range
    rng.Cells.Formula = rng.Cells.Value

End Sub

 

[omagoodness]

With data like this:

View attachment 110584



Try below code

Option Explicit
Sub clear()
If MsgBox("All cells will be cleared!", vbYesNo) = vbNo Then Exit Sub
Dim cell As Range
For Each cell In Range("A2:F8")
    If cell.Value <> "=" And cell.Value <> "+" Then cell.ClearContents
Next
End Sub
Sub randR()
If MsgBox("All cells will be randomize again!", vbYesNo) = vbNo Then Exit Sub
clear
Dim i&, ar
ar = Array("D2", "A5", "B4")
Randomize
For i = 0 To UBound(ar)
    Range(ar(i)).Value = Int(Rnd * 99) + 1
Next
Range("F2").Value = Int(Rnd * (99 - Range("D2").Value) + Range("D2").Value)
Range("D6").Value = Int(Rnd * (99 - Range("D2").Value) + Range("D2").Value)
Range("E5").Value = Int(Rnd * (99 - Range("A5").Value) + Range("A5").Value)
Range("B8").Value = Int(Rnd * (99 - Range("B4").Value) + Range("B4").Value)
Range("D8").Value = Int(Rnd * (99 - Range("B8").Value)) + 1
End Sub

puzzle.xlsm

docs.google.com

Thank you both for your prompt responses. It didn't occur to me to use VBA. I thought there was a formula condition, or something, that I was missing. I will proceed as suggested using the VBA, one way or the other. Thanks again.

 

[omagoodness]

Thank you all for your replies. I didn't think of using code to solve the issue. I guess I thought there must be a formula option I was missing. I'm not very experienced with PHP so will use VBA to generate what I need. Thanks again. Have a great day.

 

[MARK858]

I'm not very experienced with PHP

Just to say both solutions are VBA, bebo021999 has just used php code tags rather than vba code tags

 

[smozgur]

@bebo021999 - Please use the BB Codes tags on the editor to mark up your code snippets in the future to avoid confusion.