VBA:验证列表设置

Question

Sub Macro1()
'
' Macro1 Macro
'

'
    Worksheets("Drop-down").Select
    For i = 1 To 10
        ActiveSheet.Cells(i, 2).Select
        With Selection.Validation
            .Delete
            ' Error in this line
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:=Range(Worksheets("Misc").Cells(2, i), Worksheets("Misc").Cells(2, i).End)
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = ""
            .ErrorTitle = ""
            .InputMessage = ""
            .ErrorMessage = ""
            .ShowInput = True
            .ShowError = True
        End With
    Next i
End Sub

我在评论下方的行中收到错误，但我不知道如何解决它。很高兴听到一些建议。提前致谢!

Answer 1

有两个问题；首先是您缺少 End 函数的必需参数，该参数是一个命名的 Excel 常量，用于表示方向。

代替Worksheets("Misc").Cells(2, i).End在那条线上试试Worksheets("Misc").Cells(2, i).End(xlDown)
您也可以使用 xlLeft、right 或 up，但我猜 down 是您正在寻找的。

第二个问题是 Formula1 的值必须是字符串地址(我认为)，并且您正在传递一个实际的范围对象。

试试这个
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=Range(Worksheets("Misc").Cells(2, i), Worksheets("Misc").Cells(2, i).End(xlDown)).Address
这在我的机器上工作，没有错误。我当然在虚拟数据中 stub ，所以我不知道它是否适合您的工作表

编辑(回应斯坦的评论)

可能有一种更性感的方式，但这是一种很好且简单的方式(这本身也很性感)； if 语句只检查下一行中的单元格。如果它不是空的，那么它会按照你之前的解决方案进行。如果它是空的，那么它只使用那个单元格作为验证范围。

For i = 1 To 10
    If Worksheets("Misc").Cells(3, i).Value <> "" Then
        Set validationRange = Range(Worksheets("Misc").Cells(2, i), Worksheets("Misc").Cells(2, i).End(xlDown))
    Else
        Set validationRange = Worksheets("Misc").Cells(2, i)
    End If

    With Worksheets("Drop-down").Cells(i, 2).Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _ 
                    Formula1:=validationRange.Address.......

我结合了你的

Worksheet.Selection
Selection.DoStuff

对，成

Worksheet.DoStuff

我认为它看起来更好一些。

让我知道这是否适合你!