hope all is well.
I am slightly stuck on a VBA function called randbetween in Excel.
Nature of the problem is that I need to create random numbers based on a bunch of other numbers, about 50,000 other numbers in total.
The random numbers I generate must be between 1 and X. X being the other numbers in column D1:D50,000.
As an example: if cell D1 contains the number 5, then I need to create a random number between 1 and 5 in Cell A1. then move on to D2,D3,D4.....etc and create random numbers for each one accordingly, A2,A3,A4...etc.
I tried to use the following but unfortunately the offset part doesn't work. I want to dynamically work through each cell.
the code is as follows:
r = WorksheetFunction.RandBetween(1, Offset(A1, n, 9))
'where n = 2
Most grateful for any help,
Your use of OFFSET is the wrong syntax. You would need somthing like
Range("A1").Offset(RowOffset, ColumnOffset)
But there is a much better approach to achieve your stated goal. Use Range.FormulaR1C1
Sub Demo()
Dim rng As Range
' Define range
Set rng = [A1:A50000]
' Put formulas into the range
rng.FormulaR1C1 = "=RANDBETWEEN(1,RC4)"
'optional, replace formulas with values
rng.Value = rng.Value
End Sub
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments