VBA WorksheetFunction dynamic randbetween
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20446536/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA WorksheetFunction dynamic randbetween
提问by user2259146
hope all is well.
希望一切顺利。
I am slightly stuck on a VBA function called randbetween in Excel.
我有点卡在 Excel 中名为 randbetween 的 VBA 函数上。
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.
问题的本质是我需要根据一堆其他数字创建随机数，总共大约 50,000 个其他数字。
The random numbers I generate must be between 1 and X. X being the other numbers in column D1:D50,000.
我生成的随机数必须介于 1 和 X 之间。X 是 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.
例如：如果单元格 D1 包含数字 5，那么我需要在单元格 A1 中创建一个 1 到 5 之间的随机数。然后转到 D2、D3、D4..... 等并相应地为每个创建随机数，A2、A3、A4...等。
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,
非常感谢任何帮助，
回答by chris neilsen
Your use of OFFSETis the wrong syntax. You would need somthing like
您使用的OFFSET是错误的语法。你需要类似的东西
Range("A1").Offset(RowOffset, ColumnOffset)
But there is a much better approach to achieve your stated goal. Use Range.FormulaR1C1
但是有一个更好的方法来实现你的既定目标。用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
