Data Validation List generating with VBA getting 1004 error

[backsite]

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

[mancubus]

worked for me...
same code but simplified.



Sub vbax_57658_data_validation_list()

Dim i As Long

With Worksheets("HSZI AD")
For i = 2 To 6
With .Range("H97" & i).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=lista" & i
End With
Next i
End With

End Sub

[backsite]

Do you have Excel 2003? It still doesn't work for me. It may be the file is to corrupted. I cant think of anything else now

[mancubus]

i dont have excel 2003
so cant test it.