细胞验证和阵列

Question

有两个问题，首先你能提醒我如何在分配多维数组时对其进行填充吗？我总是用 for 循环来完成它，如何静态地完成它似乎让我无法理解？

但我真正想做的是使用数组来验证单元格内容。但是在下面的示例中，我只想使用第二维中的第一个元素。

即
0,0
1,0
2,0
3,0
4,0

有没有办法更新 .Add Type:=xlValidateList, Formula1:=Join(ary, ",") 行，以便它只从 5 x 4 数组返回这 5 个值？

干杯

亚伦

Sub test()


Dim ary As Variant
ReDim ary(5,4)


ary = Array("Value1", "Value2", "Value3", "test", "test2", "test3", "test4")

With ActiveSheet.Cells(1, 1).Validation
    .Delete
    .Add Type:=xlValidateList, Formula1:=Join(ary, ",")
    .IgnoreBlank = False
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = ""
    .ShowInput = True
    .ShowError = True
End With
End Sub

Answer 1

将 2D 数组大小调整为 1D 很快

下面的代码用示例数据填充 5*4 数组，然后第二个循环仅将第一个维度提取到新的 1D 数组，其行长度与初始数组相同

Sub test()

    Dim ary As Variant
    Dim X As Variant
    Dim lngRow As Long
    Dim lngCol As Long

    ReDim ary(1 To 5, 1 To 4)
    ReDim X(1 To UBound(ary, 1))

    For lngRow = 1 To UBound(ary, 1)
        For lngCol = 1 To UBound(ary, 2)
            ary(lngRow, lngCol) = "I am row " & lngRow & "  and dimension " & lngCol
        Next
    Next

    For lngRow = 1 To UBound(ary, 1)
    X(lngRow) = ary(lngRow, 1)
    Next

    With ActiveSheet.Cells(1, 1).Validation
        .Delete
        .Add Type:=xlValidateList, Formula1:=Join(X, ",")
        .IgnoreBlank = False
        .InCellDropdown = True
        .ShowInput = True
        .ShowError = True
    End With
End Sub