Random number generated, then fixed for list of values challenge

**smudgepost** · 10-22-2014, 04:31 AM

Hi all, I've a number of problems to solve for and am testing with random numbers as that's what I'll have to actually work with however, once the first random number is generated, I need to fix the number and run the rule on the next line from the (now) fixed first value, again with a random number for line two.

The idea is to test a trading strategy where all lines represent a new trade and we start with the balance from the previous trade which is why each time a random number is generated to represent the trade strategy, it needs to be locked so the next line is not suddenly changed.

The inputs are:

Starting balance and finish balance
The market price (random each time a trade is generated)
Value of trade - decided by rules, but any numbers can be used even $10 a trade to represent a transaction.

This is little more that an idea right now and I would value any support on how to generate a line item and then fix it. Maybe a macro and a button to generate as many line items as I specify and run a rule for all data. I have no experience with macros or VBA..

**Naveed Raza** · 10-22-2014, 04:42 AM

some thing like this

Sub Generate_random_Nbrs()
Dim Sht As Worksheet: Set Sht = Sheets("Sheet1")
frn = InputBox("Please Enter First Number to Fix", "First Random Nbr")
With Sht
    .Range("A2:A" & .Range("A" & .Rows.Count).End(xlUp).Row + 1).ClearContents
    .Range("A2").Value = frn
    lrn = InputBox("Please Enter No.of Lines to generate Random Numbers(Should be Morethan 3)", "Random Nbrs")
    For i = 3 To lrn
        .Range("A" & i).Value = Int((999 - frn + 1) * Rnd + frn)
            'Result: random number between frn enter nbr and 999
    Next
End With
End Sub