数据验证范围问题

Question

This Sub尝试使用下拉列表验证数据(试图防止新输入输入错误，例如：名称拼写错误(

Sub Project_Managment_Validations()
Dim ws As Worksheet
Set ws = ActiveSheet
Dim i As Integer
Dim n As Integer

For i = 1 To n
n = Range("d3").End(xlDown).Count

'Drop Down List for colum C project planner options
With ws.Range(("C3"), ws.Range("C3").End(xlDown)).Validation
.Delete
.Add Type:=xlValidateList, _
Formula1:="R. Catignani,Creutz"             'Final goal would be to have this be a refernce to a range where user can adjust the
'Options from the worksheet not developer tab.
End With
'Drop Down List for colum D Architct options

With ws.Range("D3").Offset((i - 1), 0).Validation
.Delete
.Add Type:=xlValidateList, _
Formula1:="Quinlan,Vachon,Herzog,Peccini,N/A"
End With
Next i
'Drop Down List for colum E Project Manager options

With ws.Range("E3").End(xlDown).Offset(1, 0).Validation
.Delete
.Add Type:=xlValidateList, _
Formula1:="Walsh,B. Catigani,Creutz,Logan,Sorbo, N/A"
End With
'Drop Down List for colum F Superintendent options

With ws.Range("F3").End(xlDown).Offset(1, 0).Validation
.Delete
.Add Type:=xlValidateList, _
Formula1:="Sisco,Siciliano,Perry,Asiaf,DeStefano,Jordan, N/A"
End With

End Sub

该代码有效，但只添加最后一个单元格的下拉列表，而不是之前的单元格。

这让我很困惑，因为我对范围对象的理解是从C3单元格读取列C3 中的最后一个单元格

有人能解释一下我是怎么误解这个范围物体的吗？

Answer 1

这里有一种方法，使用实用程序Sub添加验证

Sub Project_Managment_Validations()
With ActiveSheet.Rows(3).Resize(50) ' for example
AddList .Columns("C"), "R. Catignani,Creutz"
AddList .Columns("D"), "Quinlan,Vachon,Herzog,Peccini,N/A"
AddList .Columns("E"), "Walsh,B. Catigani,Creutz,Logan,Sorbo, N/A"
AddList .Columns("F"), "Sisco,Siciliano,Perry,Asiaf,DeStefano,Jordan, N/A"
End With

End Sub
'add a validation list to `rng` using `lst` as the source
Sub AddList(rng As Range, lst)
With rng.Validation
.Delete
.Add Type:=xlValidateList, Formula1:=lst
End With
End Sub