Excel 2007 : Randbetween

**Iandislikesformulas** · 08-28-2010, 07:09 AM

Hi All,

I'm currently using this formula,

=IF(ISBLANK(A1),"",IF(A1=70,RANDBETWEEN(1,1000),IF(A1=90,RANDBETWEEN(1,800))))

My problem is, once the number is generated I need it to not change every time the sheet is calculated.

**Marcol** · 08-28-2010, 08:12 AM

Is this not what it is meant to do?

From
http://office.microsoft.com/en-us/ex...005209230.aspx

Returns a random integer number between the numbers you specify. A new random integer number is returned every time the worksheet is calculated

.

You could use VBa with the change event for the sheet in question to get a stable result.

In the Worksheet Module of your chosen Sheet

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rngResult As Range
    
    If Target.Address = "$A$2" Then
        On Error GoTo RestApplication
        Application.EnableEvents = False
        
        Set rngResult = Range("B2")
        Select Case Target
            Case 70
                rngResult = WorksheetFunction.RandBetween(1, 1000)
            Case 90
                rngResult = WorksheetFunction.RandBetween(1, 800)
            Case Else
                rngResult = ""
        End Select
    End If

RestApplication:
    Err.Clear
    On Error GoTo 0
    Set rngResult = Nothing
    Application.EnableEvents = True
End Sub

Adjust the cell refs to suit.

Hope this helps

**Iandislikesformulas** · 08-29-2010, 12:48 AM

yep thats exactly what its sposed to do..... but seeing as though nothing in excel is written for stupid ppl it was the best i could find at the time and the reason i asked for help on here.
But anyway thanks for the reply, any chance you could put it in more simple terms?

**Marcol** · 08-29-2010, 06:27 AM

Hi Iandislikesformulas

No offence intended.

To use the code above.
1/. In Excel right click on the tab of the sheet you want to use the macro in.
Select View code this will open the VBa editor with the sheet module.

2/. Paste all of the given code in the resultant pane/window.

3/. Save the file and close the VBa editor

I have put together a demo workbook for you to try.
Because this book has three examples of possible solutions the code is a little diferent from Post #2.
Right click on the tab of Sheet1 as above to see the code.

The code is as easy to read as I can manage and should be fairly self explanatory.

I hope this helps.