Random Generator

[matthewjapp]

Hi there,

I have a sheet with a ranbetween function (1-5) and to update the function (not sure why, I am sure there's a better way to do it) I use a text to columns macro which seems to refresh the formula - great.
Only problem is that there is a whole load of code which runs after the event which also seems to change the random number. Is there any way to only change the random number once?

Thanks,

Matthew

 

[cb12]

Try inserting code along the lines of

    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues

obviously you'll have to select the relevant range first but that should stop any more random numbers being generated.

 

[cornflakegirl]

Pressing F9 will cause the sheet to recalculate, and this will also trigger RANDBETWEEN.

 

[RichardMGreen]

You could try either setting calculation to manual or disabling events to stop Excel calculating.

 

[matthewjapp]

is it as aimple as events.disable?

 

[ScottR]

You can switch calculations to manual in tools->options.

The code for the two things mentioned is

Application.Calculation = xlCalculationManual
Application.EnableEvents = False

But if you're going to code why not make it easier and use VBA to generate stable, random numbers that don't change anyway? You can overwrite them at will by rerunning the code anyway

cell value = int(Rnd*5)+1 generates a random number between 1 and 5...