arrays - 用数组填充下拉列表

Question

问题似乎很简单，但我找不到任何东西。我想用数组的值填充下拉选择(在 excel 中而不是在用户表单中)。
到目前为止，我创建了数组，现在我只想将它交给下拉列表。听起来很简单。

这里是创建下拉列表的代码

Worksheet("Example").Cells(i,13).Select 'original here was a  . range but i need it to be variable therefore i used cells
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:= ArrayNAme 'not working
        .IgnoreBlank = False
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True

    End With

我没有收到错误消息，但也没有出现下拉菜单。有人知道我在做什么错吗？

一些好消息，一些坏消息^^
现在创建并填充了一个下拉列表。不幸的是，填充物不太正确。

最后一个值始终是一个数字，当下一个循环到来时，数组没有被正确删除，所以图片如下:

第一个下拉:“正确值”，“正确值”“2”'不应该有数字

第二个下拉菜单:“第一个下拉菜单的值”，“第一个下拉菜单的值”，“2”，“新的正确值，...

我希望这是可以理解的。
这是我当前的代码。

Dim joinedOutput As String
Dim index As Long
For index = LBound(ArrDropdown, 1) To (UBound(ArrDropdown, 1) - 1)
    joinedOutput = joinedOutput & ArrDropdown(index) & ","
Next index
joinedOutput = joinedOutput & UBound(ArrDropdown, 1)

    Set rng = Worksheets("Transfer").Cells(j, 13)

    With rng.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=joinedOutput
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With

   Erase ArrDropdown

Answer 1

而不是使用 Selection您应该直接使用范围。看看你的代码的这个编辑:

    Dim rng As Range
    Dim ArrayName() As Variant 'this is whatever your array is (not shown in your code)

    Set rng = ThisWorkbook.Worksheets("Example").Cells(i, 13)

    With rng.Validation
        .Delete
        .Add Type:=xlValidateList, _
             AlertStyle:=xlWalidAlertStop, _
             Operator:=xlEqual, _
             Formula1:=Join(ArrayName, ",")
        .IgnoreBlank = False
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With

Join(ArrayName, ",")将获取数组的内容并将其转换为字符串，每个元素用“，”分隔