CODEWITHSUNDEEP
excelvba
NotACoderNoah
NotACoderNoah

Reputation: 5

Range Issue With Data Validation

This Sub attempts to validate data with a drop down list (trying to prevent new inputs from getting entered incorrectly example: names getting spelt wrong)

    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

the code works but only adds the drop down list for the last cell not the prior cells.

This confuses me because my understanding of my range object is reading from cell C3-last cell in column C3

Can anyone explain how I'm misunderstanding this range object?

Upvotes: 0

Views: 107

Answers (1)

Tim Williams
Tim Williams

Reputation: 166196

Here's one approach, using a utility Sub to add the validation

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

Upvotes: 1

Related Questions