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
  • 885
  • 5
  • 18
  • 30

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
  • 227,678
  • 33
  • 352
  • 470
  • 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
  • 94,018
  • 8
  • 54
  • 89
0
Dim PayRate As Variant

PayRate = Array(10, 20, 30)
indexnumber= Int(Rnd()*3
Cells(i, "E").value = PayRate(indexnumber)
David Buck
  • 3,594
  • 33
  • 29
  • 34
Armando Mercado
  • 1
  • 1