RANDBETWEEN not stopping

[Patrickwest]

I am facing a problem with RANDBETWEEN. I would appreciate if someone could help.

In the Cell B2, I keyed in a formula IF(A2=1,1,""). In the Cell C2, I keyed in the formula IF(B2=1,RANDBETWEEN(0,9),"").

When I key in the no.1 in the Cell A2, everything works fine.

Then, I fill down Cells A2, B2 and C2 all the way to A10, B10 and C10.

When I key in the no.1 in the Cell A10, the number in C2 changes and changes each time I key in a value regardless of whether no.1 or not.

Need not necessarily I key in either of Cells A2 to Cells A10, even if I key in Cell K222, the number in the Cell C2 still changes.

I do not want the value in Cell C2 or C10 or any other Cells to change for any reason after the first number is shown.

I do not know how to solve this problem.

Is there any VBA macro I need to create to solve this problem? I do not know anything about VBAs.

Thanks.

[Kenneth Hobs]

Welcome to the forum!

That is because it changes with each calculation. Change calculation mode to manual or convert it to a value using VBA. e.g.

Selection.Value = Selection.Value

That is so simple, you can even run that from VBE's Immediate Window.

Of course that could be fully automated using VBA for the Sheet's Change event for A2:A(last cell with a value). I don't see a need for column B.

[SamT]

Every time you change any cell, Excel recalculates all formulas.

Open the VBA editor, Double-Click to open the "ThisWorkbook" code page and place this code

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("A:A"), UsedRange) Is Nothing Then Exit Sub

With Target.Row 'The Entire Row of the Changed cell in Column A
  If .Cells(2) = 1 Then '.Cells(2) is second cell in that Row
   .Cells(3) = RANDBETWEEN(0, 9)
  Else: .cells(3) = 0
End If
End With

End Sub

Delete Clear Contents on Column C. Every time you Change a value in A, that cell in C will change to reflect the situation in B.

You can Inititate the Change Event by merely entering and exiting a cell in A. IOW, Select a cell in A and hold down the enter key till the selection is at the bottom of the list.