vba - Excel VBA - 使用单元格引用范围

Question

我正在尝试将 validation(使用 DataValidation:List)动态添加到工作表中的范围。我录制了一个生成以下代码的宏:

With Worksheets("Clusters").Range("C2:C100").Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:="=Managers"
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = ""
    .ShowInput = True
    .ShowError = True
End With

这对于静态范围 C2:C100 工作正常，但该列可能并不总是 C。我有一个包含列号的变量 cMANFCM。我试图编辑代码以使用它:

With Worksheets("Clusters").Range(Cells(2,cMANFCM), Cells(100, cMANFCM)).Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:="=Managers"
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = ""
    .ShowInput = True
    .ShowError = True
End With

为什么这行不通，我该如何解决？

Answer 1

您的代码适用于我的 - 我添加了一行来删除 所有 现有验证，它确实创建了新的验证而不会引发错误:

许多验证参数可能会被忽略...并且您可以选择在其他工作表处于事件状态时如何引用工作表/范围:

Option Explicit
Sub control()
    'Call changeValidation(4)
    'Call changeValidationPAlbert(5)
    Call changeValidationTWilliams(6)
End Sub

Sub changeValidation(cMANFCM As Integer)

With Excel.ThisWorkbook.Worksheets("Clusters")
    .Cells.Validation.Delete
    .Range(.Cells(2, cMANFCM), .Cells(100, cMANFCM)).Validation.Add _
            Type:=xlValidateList, _
            AlertStyle:=xlValidAlertStop, _
            Operator:=xlBetween, _
            Formula1:="=managers"
End With

End Sub
Sub changeValidationAlbert(cMANFCM As Integer)

With Excel.ThisWorkbook.Worksheets("Clusters")
    .Cells.Validation.Delete
    .Range("A2:A100").Offset(, cMANFCM - 1).Validation.Add _
            Type:=xlValidateList, _
            AlertStyle:=xlValidAlertStop, _
            Operator:=xlBetween, _
            Formula1:="=managers"
End With

End Sub
Sub changeValidationTWilliams(cMANFCM As Integer)

With Excel.ThisWorkbook.Worksheets("Clusters")
    .Cells.Validation.Delete
    .Cells(2, cMANFCM).Resize(100, 1).Validation.Add _
            Type:=xlValidateList, _
            AlertStyle:=xlValidAlertStop, _
            Operator:=xlBetween, _
            Formula1:="=managers"
End With

End Sub