时间戳

首页 > 解决方案 > 如何将数组的值分配给范围？

arrays - 如何将数组的值分配给范围？

问题描述

我正在尝试将一维数组的值分配给单元格范围。

例如; 我的数组有 23 个项目（每个项目从 1 到 5 随机），我的单元格范围是 A1 到 I7。
我想将我的数组的每个值随机分配给这个单元格范围。
我用我的数组随机化单元格值，但我的数组值没有完全分配给单元格。

Sub define_ore_body()
    Dim lb_grade As Integer, ub_grade As Integer
    Dim ore_body(1 To 23) As Variant
    Dim i As Integer, j As Integer, k As Integer
    Dim a As Object
    Dim b As Range
    
    Application.ScreenUpdating = False
    'my selected range area A1toI7
    Set b = Application.Range("A1:I7")
    Set a = Application.Cells
    
    '******* low and high ore bound ******
    lb_grade = InputBox("Enter lowest ore grade:")
    ub_grade = InputBox("Enter highest ore grade:")
    'The reason why I do it as follows is that if the random lower bound does not start from 1, 
    'the largest random number it generates is 2 more than the value I have entered, so
    If lb_grade > 1 Then
        ub_grade = ub_grade - 2
    End If
    
    '******* Random Array ******
    'array has 23 items
    For i = 1 To 23
        ore_body(i) = Int((ub_grade * Rnd) + lb_grade)
    Next i
    
    '******* filling random cells with my array******
    k = 1
    For Each a In b
        If a.Value = "" And k < 23 Then
            b(Int(7 * Rnd + 1), (8 * Rnd + 1)) = ore_body(k)
        ElseIf a.Count > 23 And k > 23 Then
        Exit For
        Else
        k = k + 1
        End If
    Next a
    
    '******* after filling cell now fill empty cells with Zero******
    For i = 1 To 7
        For j = 1 To 9
            If Cells(i, j) = "" Then
                    Cells(i, j) = 0
            Else
            End If
        Next j
    Next i
    
    '******* Coloring only containing array values******
    For i = 1 To 7
        For j = 1 To 9
            If Cells(i, j) > 0 Then
                Application.Cells(i, j).Interior.ColorIndex = 38
            Else
            End If
        Next j
    Next i
    
End Sub

标签： arraysexcelvba

解决方案

该数组包含 23 个初始化为 的项目Variant/Empty：

Dim ore_body(1 To 23) As Variant

使初始化为 63 个项目0：

Dim ore_body(1 To 63) As Long

其余代码现在将填充前 23 个元素，因为这就是循环所做的：

For i = 1 To 23

如果您希望循环遍历所有索引，请考虑使用LBound和UBound运算符以编程方式分别检索数组的下边界和上边界：

For i = LBound(ore_body) To UBound(ore_body)

请注意，您23在几个地方进行了硬编码，如果/何时需要将 23 变为 25，这将使修改变得比必要更加困难。考虑用 a 替换它的每一个出现Const：

Const ElementCount As Long = 23

然后每个实例都23可以变为ElementCount，然后当它需要变为 25 时，只有一个地方需要更改任何代码。

推荐阅读