Option Explicit
Sub Ma_Macro()
Dim a As Range
Columns("G:G").Insert Shift:=xlToRight
Columns("J:J").Insert Shift:=xlToRight
Range("G1").FormulaR1C1 = "Gamme"
Range("J1").FormulaR1C1 = "VD - VK ?"
Range("N1").FormulaR1C1 = "Part / Sté"
Columns("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(0, 4)
Columns("I:I").TextToColumns Destination:=Range("I1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(0, 4)
Columns("M:M").TextToColumns Destination:=Range("M1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(0, 4)
Columns("F:F").TextToColumns Destination:=Range("F1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(0, 1)
Range("G2").FormulaLocal = "=SI(ESTERREUR(RECHERCHEV(F2;Criteres!A:B;2;FAUX));Mod_a_creer;RECHERCHEV(F2;Criteres!A:B;2;FAUX))"
Range("G2").AutoFill Destination:=Range("G2:G" & Range("A65536").End(xlUp).Row)
With Range("G2:G" & Range("A65536").End(xlUp).Row)
.FormatConditions.Delete
.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=""Modèle à créer"""
.FormatConditions(1).Interior.ColorIndex = 3
End With
If Application.CountIf(Range("G2", Range("G65536").End(xlUp)), "Mod_a_creer") > 0 Then
MsgBox "Attention!!! Vous devez créer les Modèles manquants dans l'onglet critères"
Exit Sub
End If
Range("J2").FormulaLocal = "=SI(K2=""oui"";""Stock VD"";SI(L2=""oui"";""Stock VK"";""""))"
Range("J2").AutoFill Destination:=Range("J2:J" & Range("A65536").End(xlUp).Row)
With Range("N2:S" & Range("A65536").End(xlUp).Row).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=P_Ste"
End With
With Range("E65536").End(xlUp)(4)
.Value = "Nb"
.Font.Bold = True
End With
Range("D1", Range("D65536").End(xlUp)).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("D1").End(xlDown)(4), Unique:=True
With Range("C65536").End(xlUp)(4)
.Value = "Nb"
.Font.Bold = True
End With
Range("B1", Range("B65536").End(xlUp)).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("B1").End(xlDown)(4), Unique:=True
With Range("E65536").End(xlUp)(2)
.FormulaLocal = "=NB.SI(" & Range("D1:D" & Range("D1").End(xlDown).Row).Address & ";" & .Offset(0, -1).Address(0, 0) & ")"
If .Value < Range("D2", Range("D2").End(xlDown)).Count Then
.AutoFill Destination:=Range(.Address & ":E" & Range("D65536").End(xlUp).Row)
End If
End With
With Range("C65536").End(xlUp)(2)
.FormulaLocal = "=NB.SI(" & Range("B1:B" & Range("B1").End(xlDown).Row).Address & ";" & .Offset(0, -1).Address(0, 0) & ")"
If .Value < Range("B2", Range("B2").End(xlDown)).Count Then
.AutoFill Destination:=Range(.Address & ":C" & Range("B65536").End(xlUp).Row)
End If
End With
End Sub