Excel VBA - 使用单元格引用范围
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15123625/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel VBA - Referencing Range using Cells
提问by user1825257
I am trying to dynamically add validation(using DataValidation:List) to ranges in a worksheet. I recorded a macro that produced the following code:
我正在尝试动态添加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
This was working fine for the static range C2:C100, but the column may not be always be C. I have a variable cMANFCM that contains the column number. I tried to edit the code to use this:
这对于静态范围 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
Why doesn't this work and how do I fix it?
为什么这不起作用，我该如何解决？
回答by whytheq
Your code works on mine - I've added a line to delete allexisting validation and it does create new validation without throwing an error:
您的代码适用于我的 - 我添加了一行来删除所有现有验证，它确实创建了新的验证而不会引发错误：
Lots of the validation arguments can probably be ignored...and you've got a choice of how to reference the sheet/range when other sheets are active:
许多验证参数可能会被忽略……当其他工作表处于活动状态时，您可以选择如何引用工作表/范围：
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
