manual calculatin of the RAND function

Originally Posted by jrtaylor

...Is there a way ...

Not using worksheet functions. Your best bet is a macro (which you can assign to a button). If possible, upload a copy of your workbook.
Remove any sensitive or extraneous info, mainly just show us the formulas that use the RAND() function and the cells that contain them.

To attach a workbook:
Click Edit Post (or just start a new reply.)
Click Go Advanced
Scroll down to Manage Attachments and click.
Now just Browse for your file, then click Upload. Simple!

Please verify:
1) Column C is checked from row 6 down. (Macro can find the bottom each time it runs.)
2) Each "buy" or "sell" in column C gets a code in column A. (Are there any other entry types in column C?)
3) Codes start with a set number (Note- Stored in a cell? or in macro itself? or as Defined Name in Name Manager?)
4) Starting from top, each new code adds 1-300 to previous value.

I'm not certain about your process, so this might or might not "fit". The following code responds each time you change a cell on the worksheet. If column C cells change to "buy" or "sell" AND there's no existing Ticket #, one is added. If they change to "", the corresponding Ticket #'s are cleared too. Let me know what you think.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim V As Variant
    Dim C As Range
    Dim A As Range
    Dim M As Double
    Dim wf As WorksheetFunction
    
    With Target
    
    If .Columns.Count = 1 And .Column = 3 And .Row >= 6 Then
        Set wf = Application.WorksheetFunction
        M = wf.Max(Range("A:A"))
        On Error Resume Next
        For Each C In .Cells
            Set A = C.Offset(0, -2)
            V = LCase(C)
            If V = "buy" Or V = "sell" Then
                If A = "" Then
                    M = M + wf.RandBetween(1, 300)
                    A = M
                End If
            ElseIf V = "" Then
                A.ClearContents
            End If

        Next
    End If
    
    End With
End Sub

To use this code:
-Right-click on your worksheet Tab
-Select View Code. This takes you to the Visual Basic Editor.
-Paste the code in the Module that appears.
-Close the Editor Window X to return to Excel.

-Now click the File tab on the Main Ribbon
-Select Save As and pick your folder.
-Click in the Save as Type: box
-Select Excel Macro-Enabled Workbook (*xlsm)
-Change the filename (optional), then click Save
NOTE- The NEW macro-enabled (.xlsm) workbook does not affect your existing .xlsx file. They are completely separate.
You should eventually delete the old .xlsx version to avoid confusion.

Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee