1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42
| Sub Creation_Acceuil_Liste()
Dim Last_Row_Z As Long
Dim Last_Row_New_Z As Long
Dim Adresse1 As String
Sheets("Rapport 1").Range("A:A").Copy Sheets("Rapport 1").Range("Z:Z")
Last_Row_Z = Sheets("Rapport 1").Cells(Sheets("Rapport 1").Rows.Count, "Z").End(xlUp).Row
Sheets("Rapport 1").Range("Z2:Z" & Last_Row_Z).RemoveDuplicates Columns:=1, Header:=xlNo
Last_Row_New_Z = Sheets("Rapport 1").Cells(Sheets("Rapport 1").Rows.Count, "Z").End(xlUp).Row
ActiveWorkbook.Names.Add _
Name:="ListeD1", _
RefersTo:="='Rapport 1'!$Z$2" 'ListeD1 existe
ActiveWorkbook.Names.Add _
Name:="ListeDZ", _
RefersTo:="='Rapport 1'!$Z$2: $Z$" & Last_Row_New_Z 'ListeDZ existe
ActiveWorkbook.Names.Add _
Name:="ListeD", _
RefersToR1C1:="=OFFSET(ListeD1,0,0,COUNTA(ListeDZ)-1,1)" 'ListeD n'existe pas
With Sheets("Accueil").Range("C3:D4").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=SI(C3<>"""";DECALER(ListeD1;EQUIV(C3&"" * "";ListeD;0)-1;;SOMMEPROD((STXT(ListeD;1;NBCAR(C3))=TEXTE(C3;""0""))*1));ListeD)" 'L'erreur vient d'ici
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = False
End With
End Sub |
Partager