Randomize List Based on Value

[Littlemalky]

Hi, I'm trying to create a financial model simulating different scenarios or conditions. I have a list of 50 accounts. I want to create an input field where I set the value anywhere from 0 to 50. So say I choose 30, then I want a 1 to appear next to 30 of the 50 accounts (randomly). I will use this to create a formula to pull different sales values based on that. I'd image I need VB for this. Does anyone have any ideas?

 

[Rick Rothstein]

I have a list of 50 accounts.

In what column?

...then I want a 1 to appear next to 30 of the 50 accounts (randomly)

If the answer to the first question is not Column A, then next to them in what column?

 

[Littlemalky]

Hi Rick, thank you for responding! The accounts are listed in B7:B56. However, there is a lot of data in between and I'm trying to get this random number generated in column 'AB'.

 

[Rick Rothstein]

Give this macro a try...

Sub ThirtyRandomAccounts()
  Dim LastRow As Long, Cnt As Long, RandomIndex As Long
  Dim Tmp As Variant, Arr As Variant
  LastRow = Range("B7").End(xlDown).Row
  Arr = Evaluate("ROW(B7:B" & LastRow & ")")
  For Cnt = UBound(Arr) To LBound(Arr) Step -1
    RandomIndex = Int((Cnt - LBound(Arr) + 1) * Rnd + LBound(Arr))
    Tmp = Arr(RandomIndex, 1)
    Arr(RandomIndex, 1) = Arr(Cnt, 1)
    Arr(Cnt, 1) = Tmp
  Next
  For Cnt = 1 To 30
    Cells(Arr(Cnt, 1), "AB").Value = 1
  Next
End Sub

 

[Littlemalky]

Hi Rick, thank you for responding. This set every cell within my range to a value of 1 instead of creating random numbers.