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 43 44 45 46 47 48
| Sub Test()
Dim TS As Range, CP As Range, DS As Range, DA As Range, DR As Range, VD As Range, CA As Range, C As Range, Plage As Range
With Worksheets("LAPS")
Set TS = .Rows(1).Find(what:="TS", LookIn:=xlValues, lookat:=xlWhole)
Set CP = .Rows(1).Find(what:="CP", LookIn:=xlValues, lookat:=xlWhole)
Set DS = .Rows(1).Find(what:="DS", LookIn:=xlValues, lookat:=xlWhole)
Set DA = .Rows(1).Find(what:="DA", LookIn:=xlValues, lookat:=xlWhole)
Set DR = .Rows(1).Find(what:="DR", LookIn:=xlValues, lookat:=xlWhole)
Set VD = .Rows(1).Find(what:="VD", LookIn:=xlValues, lookat:=xlWhole)
Set CA = .Rows(1).Find(what:="CA", LookIn:=xlValues, lookat:=xlWhole)
If TS Is Nothing Then MsgBox "TS Column Not Found": Exit Sub
If CP Is Nothing Then MsgBox "CP Column Not Found": Exit Sub
If DS Is Nothing Then MsgBox "DS Column Not Found": Exit Sub
If DA Is Nothing Then MsgBox "DA Column Not Found": Exit Sub
If DR Is Nothing Then MsgBox "DR Column Not Found": Exit Sub
If VD Is Nothing Then MsgBox "VD Column Not Found": Exit Sub
If CA Is Nothing Then MsgBox "CA Column Not Found": Exit Sub
Set C = .Cells(1, Columns.Count).End(xlToLeft).Offset(0, 1)
C.Value = "KEY"
C.Offset(1, 0).FormulaLocal = "=SI(" & .Cells(2, TS.Column).Address(0, 0) & _
"=""NOK"";""NOK"";SI(OU(" & .Cells(2, CP.Column).Address(0, 0) & _
"=""UUUXXX"";" & .Cells(2, CP.Column).Address(0, 0) & _
"=""YYYXXX"";" & .Cells(2, CP.Column).Address(0, 0) & _
"=""WWWXXX"";" & .Cells(2, CP.Column).Address(0, 0) & _
"=""ZZZXXX"");""MOUT"";SI(OU(" & .Cells(2, CP.Column).Address(0, 0) & _
"=""AAAXXX"";" & .Cells(2, CP.Column).Address(0, 0) & _
"=""BBBXXX"";" & .Cells(2, CP.Column).Address(0, 0) & _
"=""GGGXXX"");" & .Cells(2, DS.Column).Address(0, 0) & "&" & .Cells(2, DA.Column).Address(0, 0) & _
"&GAUCHE(" & .Cells(2, CP.Column).Address(0, 0) & _
";3)&" & .Cells(2, DR.Column).Address(0, 0) & "&" & .Cells(2, VD.Column).Address(0, 0) & _
";SI(" & .Cells(2, CP.Column).Address(0, 0) & _
"=""XXXCCC"";" & .Cells(2, DS.Column).Address(0, 0) & "&" & .Cells(2, DA.Column).Address(0, 0) & _
"&DROITE(" & .Cells(2, CP.Column).Address(0, 0) & _
";3)&" & .Cells(2, DR.Column).Address(0, 0) & "&" & .Cells(2, VD.Column).Address(0, 0) & _
";SI(OU(" & .Cells(2, CP.Column).Address(0, 0) & _
"=""XXXDDD"";" & .Cells(2, CP.Column).Address(0, 0) & _
"=""XXXEEE"";" & .Cells(2, CP.Column).Address(0, 0) & _
"=""XXXFFF"");SI(" & .Cells(2, DS.Column).Address(0, 0) & _
"=""N"";""K"";""N"")&" & .Cells(2, CA.Column).Address(0, 0) & _
"&DROITE(" & .Cells(2, CP.Column).Address(0, 0) & _
";3)&" & .Cells(2, DR.Column).Address(0, 0) & "&" & .Cells(2, VD.Column).Address(0, 0) & _
";""FILL"")))))"
Set Plage = C.Offset(1, 0).Resize(.Cells(Rows.Count, "A").End(xlUp).Row - 1, 1)
C.Offset(1, 0).AutoFill Destination:=Plage
End With
Set Plage = Nothing: Set C = Nothing
Set TS = Nothing: Set CP = Nothing: Set DS = Nothing: Set DA = Nothing: Set DR = Nothing: Set VD = Nothing
End Sub |