VBA根据其他单元格通过随机数填充单元格

I am working with Excel 2013 and I need to fill the range of cells by random numbers. To be exact, Range(B1:B4) fill by numbers in order to value of cells in previous column, I mean in Range(A1:A4). I really have no idea how to fill that using VBA if there is that condition, otherwise it's simple.

我正在使用Excel 2013,我需要通过随机数填充单元格范围。确切地说,范围(B1:B4)按数字填充以便在前一列中的单元格值,我的意思是在范围(A1:A4)中。如果有这种情况,我真的不知道如何使用VBA填充它,否则它很简单。

Here is a scetch of cells

这是细胞的一种形态

# |    A     |   B   |
----------------------
1 |   Yes    |   1   |
----------------------
2 |   No     |   2   |
----------------------
3 |   Maybe  |   3   |
----------------------
4 |   No     |   2   |
----------------------

2 个解决方案

#1

If all you need to do is set this with vba, this should give you values 1, 2, or 3:

如果你需要做的只是用vba设置它,这应该给你值1,2或3:

Range("B1:B4").Formula = "=RANDBETWEEN(1,3)"

If you only need an Excel formula, you can always just paste =RANDBETWEEN(1,3) into the formula bar.

如果您只需要Excel公式,则可以始终将= RANDBETWEEN(1,3)粘贴到公式栏中。

If you're trying to define column B values based on column A values, just use:

如果您尝试根据A列值定义B列值,只需使用:

Range("B1:B4").Formula = "=IF(A1 = ""Yes"", 1, IF(""No"", 2, If(""Maybe"", 3, ""ERROR"")))"

If neither of those are what you want, you're going to have to clarify better.

如果这些都不是你想要的,你将不得不更好地澄清。

#2

If all you need is random numbers, you don't need VBA. Just set your cell formula equal to:

如果您只需要随机数,则不需要VBA。只需将您的单元格公式设置为:

"=RANDBETWEEN(1,3)"

However, your random numbers will change every time your worksheet is calculated. To avoid this, you can define the following sub and associate it, for example, with an action button:

但是,每次计算工作表时,随机数都会更改。为避免这种情况,您可以定义以下子项并将其关联,例如,使用操作按钮:

Sub makeRand()

    Dim targetRange As Range
    Dim xlCell As Range
    Dim upperBound As Integer
    Dim lowerBound As Integer


    Set targetRange = Range("B1:B4")
    upperBound = 3
    lowerBound = 1

    Randomize

    For Each xlCell In targetRange
        xlCell.Value = Int((upperBound - lowerBound + 1) * Rnd + lowerBound)
    Next xlCell


End Sub

#1