backsite
11-07-2016, 02:48 AM
Hi guys. I would like to ask for some help regarding a data validation list. Im trying to create multiple dynamic data validation lists. Im almost done but at the last step i cannot multiple the last bit of data validation lists that have different named ranges as formulas:
Sub Sample11212()
Dim i As Long
Dim sist As String
For i = 2 To 6
Sheets("HSZI AD").Range("H97" & i).Select
Selection.Activate
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=lista" & i
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Next i
End Sub
It should convert H972 through H976 into lista2, lista3,lista4 respectively. All those named ranges exist.
1004 error comes at this two lines:
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=lista" & i
Thank you for any help in advance
Sub Sample11212()
Dim i As Long
Dim sist As String
For i = 2 To 6
Sheets("HSZI AD").Range("H97" & i).Select
Selection.Activate
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=lista" & i
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Next i
End Sub
It should convert H972 through H976 into lista2, lista3,lista4 respectively. All those named ranges exist.
1004 error comes at this two lines:
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=lista" & i
Thank you for any help in advance