PDA

View Full Version : RANDBETWEEN not stopping



Patrickwest
09-21-2016, 06:07 PM
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
09-21-2016, 07:35 PM
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.ValueThat 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
09-21-2016, 07:50 PM
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.