Amélioration Code VBA-Recherche v

Bonjour à tous,

je cherche un code afin d'améliorer la syntaxe de mon code suivant :

<Sub Affectation() 'ETAPE 4
Dim b As Single
Application.ScreenUpdating = False
Sheets("RESULTAT").Select
b = Cells(Rows.Count, 2).End(xlUp).Row
Columns("E:E").Select
Selection.Insert Shift:=xlToRight
Range("E1").Value = "NATURE JAL"
' Couleur ajoutée avec fond blanc à la colonne nouvellement créée
Range("E1").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 10027161
.TintAndShade = 0
.PatternTintAndShade = 0
End With
With Selection.Font
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With
Selection.Font.Bold = True
'Recherche V pour mettre la nature des journaux en face de chaque code JAL
Range("E2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],JOURNAUX!C[-1]:C,2,FALSE)"
Range("E2").Select
Selection.AutoFill Destination:=Range("E2:E" & b)
Range("E2:E" & b).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

'Recherchev pour affecter les éléments en fonction du MAPPING


Range("O2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],MAPPING!C[-13]:C[-12],2,FALSE)"
Range("P2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-4],MAPPING!C[-14]:C[-13],2,FALSE)"
Range("Q2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-4],MAPPING!C[-15]:C[-13],3,FALSE)"
Range("R2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-5],MAPPING!C[-16]:C[-12],5,FALSE)"
Range("S2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-6],MAPPING!C[-17]:C[-14],4,FALSE)"
Range("T2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-8],MAPPING!C[-18]:C[-13],6,FALSE)"
Range("U2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-9],MAPPING!C[-19]:C[-13],7,FALSE)"
Range("V2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-9],MAPPING!C[-20]:C[-15],6,FALSE)"
Range("W2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-10],MAPPING!C[-21]:C[-15],7,FALSE)"
Range("O2:W2").Select
Selection.AutoFill Destination:=Range("O2:W" & b)
Range("O2:W" & b).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("K1:W1").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 10027161
.TintAndShade = 0
.PatternTintAndShade = 0
End With
With Selection.Font
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With
Selection.Font.Bold = True


Cells.Select
Cells.EntireColumn.AutoFit
Range("A1").Select
Selection.ClearContents
Range("A1").Value = "DATE"
Application.ScreenUpdating = True
End Sub

Merci d'avance

Bonjour,

En VBA, il faut bannir les .Select et Selection chaque fois que c'est possible, c'est-à-dire toujours (ou presque) .

La variable b représente un nombre de lignes donc un entier, dans ce cas de type Long car susceptible de dépasser 65535, mais en aucun cas un nombre décimal (Single)

Option Explicit
Sub Affectation()  'ETAPE 4
Dim b As Long

  Application.ScreenUpdating = False
  With Worksheets("RESULTAT")
    .Columns("E:E").Insert Shift:=xlToRight
    With .Range("E1")
      .Value = "NATURE JAL"
      .Interior.Color = 10027161
      .Font.ThemeColor = xlThemeColorDark1
      .Font.Bold = True
    End With
    b = .Cells(.Rows.Count, 2).End(xlUp).Row
    With .Range("E2:E" & b)
      .FormulaR1C1 = "=VLOOKUP(RC[-1],JOURNAUX!C[-1]:C,2,FALSE)"
      .Value = .Value
    End With
    .Range("O2:O" & b).FormulaR1C1 = "=VLOOKUP(RC[-2],MAPPING!C[-13]:C[-12],2,FALSE)"
    .Range("P2:P" & b).FormulaR1C1 = "=VLOOKUP(RC[-4],MAPPING!C[-14]:C[-13],2,FALSE)"
    .Range("Q2:Q" & b).FormulaR1C1 = "=VLOOKUP(RC[-4],MAPPING!C[-15]:C[-13],3,FALSE)"
    .Range("R2:R" & b).FormulaR1C1 = "=VLOOKUP(RC[-5],MAPPING!C[-16]:C[-12],5,FALSE)"
    .Range("S2:S" & b).FormulaR1C1 = "=VLOOKUP(RC[-6],MAPPING!C[-17]:C[-14],4,FALSE)"
    .Range("T2:T" & b).FormulaR1C1 = "=VLOOKUP(RC[-8],MAPPING!C[-18]:C[-13],6,FALSE)"
    .Range("U2:U" & b).FormulaR1C1 = "=VLOOKUP(RC[-9],MAPPING!C[-19]:C[-13],7,FALSE)"
    .Range("V2:V" & b).FormulaR1C1 = "=VLOOKUP(RC[-9],MAPPING!C[-20]:C[-15],6,FALSE)"
    .Range("W2:W" & b).FormulaR1C1 = "=VLOOKUP(RC[-10],MAPPING!C[-21]:C[-15],7,FALSE)"
    With Range("O2:W" & b)
      .Value = .Value
    End With
    With .Range("K1:W1")
      .Interior.Color = 10027161
      .Font.ThemeColor = xlThemeColorDark1
    End With
    .Columns.AutoFit
    .Range("A1").Value = "DATE"
  End With

End Sub

Merci Patrice !!!