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?
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.
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.
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.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.
Dim PayRate As Variant
PayRate = Array(10, 20, 30)
indexNumber = Int(Rnd()*3)
Cells(i, "E").value = PayRate(indexNumber)
PayRate(Int((2 - 0 + 1) * Rnd + 0))