0

I've stormed the internet and still have not found any solution to this question:

I have an array like so;

Dim PayRate As Variant
PayRate = Array(10, 20, 30)

Cells(i, "E").value = PayRate(Int((2 - 1 + 1) * Rnd + 1))

but this will only give me 20 and 30 from the array (elements 1 and 2), I also want element 0 = 10 in the mix ? How do I got about doing that?

Thatdude1
  • 905
  • 5
  • 18
  • 31

3 Answers3

4

This will do it: Int(Rnd() * 3) + 1

Rnd() returns a uniformly distributed random number from and including 0 to and not including 1.

Important: you must have Option Base 1 at the top of the module so your PayRate array has lowest bound of 1. Seems like you have this given your question text.

Bathsheba
  • 231,907
  • 34
  • 361
  • 483
  • after all +1 for simplicity – Kazimierz Jawor Oct 01 '13 at 13:55
  • Agree it's simple but `Int(Rnd() * 3) + 1` never returns `0`. You therefore have to use `Option Base 1` which is typically not the default(?) i.e. most assume the first element of an array is indexed at 0. – Alex P Oct 01 '13 at 14:06
  • Setting `Option Base 1` and then adding 1 to the result of the random number to convert it to a 1 based index isn't a good idea. `Option Base 1` changes the sematics of ***the entire code module it's declared in***. Just use `Int(Rnd() * 3)` with the default indexing. – Comintern Feb 24 '17 at 15:11
0

Consider:

Sub dural()
Dim PayRate As Variant
PayRate = Array(10, 20, 30)
i = 1
N = Application.WorksheetFunction.RandBetween(LBound(PayRate), UBound(PayRate))
Cells(i, "E").Value = PayRate(N)
End Sub

I like this because it is independent of the number of elements in the array or whether it is zero-based or one-based.

Gary's Student
  • 95,722
  • 10
  • 59
  • 99
0
Dim PayRate As Variant

PayRate = Array(10, 20, 30)
indexnumber= Int(Rnd()*3
Cells(i, "E").value = PayRate(indexnumber)
David Buck
  • 3,752
  • 35
  • 31
  • 35
Armando Mercado
  • 1
  • 1