Besoin d'aide pour "nettoyer" mon code VBA EXCEL

Résolu/Fermé

Utilisateur anonyme - 7 oct. 2018 à 18:10
yg_be Messages postés 22754 Date d'inscription lundi 9 juin 2008 Statut Contributeur Dernière intervention 4 mai 2024 - 7 oct. 2018 à 20:20

Bonjour,

J'ai réalisé une macro qui a pour fonction de "formater" un document dans le but de l'importer dans une base de données.

J'ai besoin de votre aide pour m'aider à nettoyer le code, entre autre de me débarrasser des .SELECT.

Je vous remercie par avance,

Je reste à disposition pour toutes questions.

                                   
                                    
Sub Generate()
'
' Generate Macro                    

'
Application.ScreenUpdating = False

With ActiveSheet.UsedRange

    ' Copier/Coller les colonnes + creation des colonnes RechercheV
    
    Sheets("OLIVE").Select
    Columns("X:X").Select
    Selection.Copy
    Sheets("IMPORT").Select
    Columns("A:A").Select
    ActiveSheet.Paste
    Sheets("OLIVE").Select
    Columns("Y:Y").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("IMPORT").Select
    Columns("B:B").Select
    ActiveSheet.Paste
    Sheets("OLIVE").Select
    Columns("P:P").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("IMPORT").Select
    Columns("C:C").Select
    ActiveSheet.Paste
    Sheets("OLIVE").Select
    Columns("W:W").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("IMPORT").Select
    Columns("D:D").Select
    ActiveSheet.Paste
    Sheets("OLIVE").Select
    Columns("AD:AD").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("IMPORT").Select
    Columns("E:E").Select
    ActiveSheet.Paste
    Columns("E:E").Select
    Application.CutCopyMode = False
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("G:G").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("J:J").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("E2").Select
    
    'RechercheVs
    
    ActiveCell.FormulaR1C1 = _
        "=RECHERCHEV('IMPORT'!D2;'REF FILE'!A:B;2;faux)"
    Range("E2").Select
    Selection.NumberFormat = "General"
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP('IMPORT'!RC[-1],'REF FILE'!C[-4]:C[-3],2,FALSE)"
    Range("E2").Select
    Selection.AutoFill Destination:=Range("E2:E" & .Rows(.Rows.Count).Row), Type:=xlFillDefault
    Range("E2:E" & .Rows(.Rows.Count).Row).Select
    
    
    Range("G2").Select
    ActiveCell.FormulaR1C1 = "=RECHERCHEV(F2;'REF FILE 2'!A:D;4;faux)"
    Range("G2").Select
    Selection.NumberFormat = "General"
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-1],'REF FILE 2'!C[-6]:C[-3],4,FALSE)"
    Range("G2").Select
    Selection.AutoFill Destination:=Range("G2:G" & .Rows(.Rows.Count).Row), Type:=xlFillDefault
    Range("G2:G" & .Rows(.Rows.Count).Row).Select
   
    Range("J2").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-4],'REF FILE 2'!C[-9]:C[-8],2,FALSE)"
    Range("J2").Select
    Selection.AutoFill Destination:=Range("J2:J" & .Rows(.Rows.Count).Row), Type:=xlFillDefault
    Range("J2:J" & .Rows(.Rows.Count).Row).Select
    Range("K2").Select
    
   
    
    
'Volume
    Range("J2:J" & .Rows(.Rows.Count).Row).Select
    Selection.Cut
    Range("I2").Select
    ActiveSheet.Paste
    Columns("J:J").Select
    Selection.Delete Shift:=xlToLeft
    
'Formating

    
 Range("A1").Select
    ActiveCell.FormulaR1C1 = "Colonne 1"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "Colonne 2"
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "Colonne 3"
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "Colonne 4"
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "Colonne 5"
    Range("F1").Select
    ActiveCell.FormulaR1C1 = "Colonne 6"
    Range("G1").Select
    ActiveCell.FormulaR1C1 = "Colonne 7"
    Range("H1").Select
    ActiveCell.FormulaR1C1 = "Colonne 8"
    Range("I1").Select
    ActiveCell.FormulaR1C1 = "Colonne 9"
    
    
    Columns("A:I").Select
    With Selection.Font
        .Name = "Calibri"
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor
    End With
    With Selection.Font
        .Name = "Calibri"
        .Size = 11
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor
    End With
    Selection.Font.Bold = False
    Selection.Font.Bold = True
    With Selection.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    With Selection.Font
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
    End With
    With Selection
        .VerticalAlignment = xlBottom
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Font.Bold = False
    
    
  
' Calcul 1
'

'
    Range("J2").Select
   Selection.NumberFormat = "General"
    ActiveCell.FormulaR1C1 = _
        "=SUMIFS('OLIVE'!C[21],'OLIVE'!C[14],'IMPORT'!RC[-9],'OLIVE'!C[15],'IMPORT'!RC[-8],'OLIVE'!C[6],'IMPORT'!RC[-7],'OLIVE'!C[13],'IMPORT'!RC[-6],'OLIVE'!C[20],'IMPORT'!RC[-4],'OLIVE'!C[22],""Tonne"",'OLIVE'!C[17],""Traitement/Transfert"")+SUMIFS('OLIVE'!C[21],'OLIVE'!C[14],'IMPORT'!RC[-9],'OLIVE'!C[15],'IMPORT'!RC[-8],'OLIVE'!C[6],'IMPORT'!RC[-7],'OLIVE'!C[13],'IMPORT'!RC[-6],'OLIVE'!C[20],'IMPORT'!RC[-4],'OLIVE'!C[22],""Tonne"",'OLIVE'!C[17],""Traitement/Transfert"")"
    Range("J2").Select
    Selection.AutoFill Destination:=Range("J2:J" & .Rows(.Rows.Count).Row), Type:=xlFillDefault
    

'Calcul 2
'
'
    Range("V2").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-19],'OLIVE'!C[-6]:C[14],21,FALSE)"
    Range("V2").Select
    Selection.AutoFill Destination:=Range("V2:V" & .Rows(.Rows.Count).Row), Type:=xlFillDefault
    Range("V2:V" & .Rows(.Rows.Count).Row).Select


'Calcul 3
'
    Range("X2").Select
    ActiveCell.FormulaR1C1 = _
        "=SUMIFS('OLIVE'!C[7],'OLIVE'!C,'IMPORT'!RC[-23],'OLIVE'!C[1],'IMPORT'!RC[-22],'OLIVE'!C[-8],'IMPORT'!RC[-21],'OLIVE'!C[-1],'IMPORT'!RC[-20],'OLIVE'!C[6],'IMPORT'!RC[-18],'OLIVE'!C[3],""Traitement/Transfert"",'OLIVE'!C[8],""Tonne"")"
    Range("X2").Select
    Selection.AutoFill Destination:=Range("X2:X" & .Rows(.Rows.Count).Row), Type:=xlFillDefault
    Range("X2:X" & .Rows(.Rows.Count).Row).Select
    



'Calcul4
Range("AF2").Select
    ActiveCell.FormulaR1C1 = _
        "=SUMIFS('OLIVE'!C[7],'OLIVE'!C,'IMPORT'!RC[-23],'OLIVE'!C[1],'IMPORT'!RC[-22],'OLIVE'!C[-8],'IMPORT'!RC[-21],'OLIVE'!C[-1],'IMPORT'!RC[-20],'OLIVE'!C[6],'IMPORT'!RC[-18],'OLIVE'!C[3],""Traitement/Transfert"",'OLIVE'!C[8],""Tonne"")"
    Range("AF2").Select
    Selection.AutoFill Destination:=Range("AF2:AF" & .Rows(.Rows.Count).Row), Type:=xlFillDefault
    Range("AF2:X" & .Rows(.Rows.Count).Row).Select

'Calcul 5
Range("AH2").Select
    ActiveCell.FormulaR1C1 = _
        "=SUMIFS('OLIVE'!C[7],'OLIVE'!C,'IMPORT'!RC[-23],'OLIVE'!C[1],'IMPORT'!RC[-22],'OLIVE'!C[-8],'IMPORT'!RC[-21],'OLIVE'!C[-1],'IMPORT'!RC[-20],'OLIVE'!C[6],'IMPORT'!RC[-18],'OLIVE'!C[3],""Traitement/Transfert"",'OLIVE'!C[8],""Tonne"")"
    Range("AH2").Select
    Selection.AutoFill Destination:=Range("AH2:AH" & .Rows(.Rows.Count).Row), Type:=xlFillDefault
    Range("AH2:X" & .Rows(.Rows.Count).Row).Select
	
'Calcul 6
Range("AI2").Select
    ActiveCell.FormulaR1C1 = _
        "=SUMIFS('OLIVE'!C[7],'OLIVE'!C,'IMPORT'!RC[-23],'OLIVE'!C[1],'IMPORT'!RC[-22],'OLIVE'!C[-8],'IMPORT'!RC[-21],'OLIVE'!C[-1],'IMPORT'!RC[-20],'OLIVE'!C[6],'IMPORT'!RC[-18],'OLIVE'!C[3],""Traitement/Transfert"",'OLIVE'!C[8],""Tonne"")"
    Range("AI2").Select
    Selection.AutoFill Destination:=Range("AI2:AI" & .Rows(.Rows.Count).Row), Type:=xlFillDefault
    Range("AI2:X" & .Rows(.Rows.Count).Row).Select
'Calcul 7
Range("AJ2").Select
    ActiveCell.FormulaR1C1 = _
        "=SUMIFS('OLIVE'!C[7],'OLIVE'!C,'IMPORT'!RC[-23],'OLIVE'!C[1],'IMPORT'!RC[-22],'OLIVE'!C[-8],'IMPORT'!RC[-21],'OLIVE'!C[-1],'IMPORT'!RC[-20],'OLIVE'!C[6],'IMPORT'!RC[-18],'OLIVE'!C[3],""Traitement/Transfert"",'OLIVE'!C[8],""Tonne"")"
    Range("AJ2").Select
    Selection.AutoFill Destination:=Range("AJ2:AJ" & .Rows(.Rows.Count).Row), Type:=xlFillDefault
    Range("AJ2:X" & .Rows(.Rows.Count).Row).Select
'Calcul 8
Range("AK2").Select
    ActiveCell.FormulaR1C1 = _
        "=SUMIFS('OLIVE'!C[7],'OLIVE'!C,'IMPORT'!RC[-23],'OLIVE'!C[1],'IMPORT'!RC[-22],'OLIVE'!C[-8],'IMPORT'!RC[-21],'OLIVE'!C[-1],'IMPORT'!RC[-20],'OLIVE'!C[6],'IMPORT'!RC[-18],'OLIVE'!C[3],""Traitement/Transfert"",'OLIVE'!C[8],""Tonne"")"
    Range("AK2").Select
    Selection.AutoFill Destination:=Range("AK2:AK" & .Rows(.Rows.Count).Row), Type:=xlFillDefault
    Range("AK2:X" & .Rows(.Rows.Count).Row).Select
'Calcul 9
Range("AL2").Select
    ActiveCell.FormulaR1C1 = _
        "=SUMIFS('OLIVE'!C[7],'OLIVE'!C,'IMPORT'!RC[-23],'OLIVE'!C[1],'IMPORT'!RC[-22],'OLIVE'!C[-8],'IMPORT'!RC[-21],'OLIVE'!C[-1],'IMPORT'!RC[-20],'OLIVE'!C[6],'IMPORT'!RC[-18],'OLIVE'!C[3],""Traitement/Transfert"",'OLIVE'!C[8],""Tonne"")"
    Range("AL2").Select
    Selection.AutoFill Destination:=Range("AL2:AL" & .Rows(.Rows.Count).Row), Type:=xlFillDefault
    Range("AL2:X" & .Rows(.Rows.Count).Row).Select
'Calcul 10
Range("AM2").Select
    ActiveCell.FormulaR1C1 = _
        "=SUMIFS('OLIVE'!C[7],'OLIVE'!C,'IMPORT'!RC[-23],'OLIVE'!C[1],'IMPORT'!RC[-22],'OLIVE'!C[-8],'IMPORT'!RC[-21],'OLIVE'!C[-1],'IMPORT'!RC[-20],'OLIVE'!C[6],'IMPORT'!RC[-18],'OLIVE'!C[3],""Traitement/Transfert"",'OLIVE'!C[8],""Tonne"")"
    Range("AM2").Select
    Selection.AutoFill Destination:=Range("AM2:AM" & .Rows(.Rows.Count).Row), Type:=xlFillDefault
    Range("AM2:X" & .Rows(.Rows.Count).Row).Select
'Calcul 11
Range("AN2").Select
    ActiveCell.FormulaR1C1 = _
        "=SUMIFS('OLIVE'!C[7],'OLIVE'!C,'IMPORT'!RC[-23],'OLIVE'!C[1],'IMPORT'!RC[-22],'OLIVE'!C[-8],'IMPORT'!RC[-21],'OLIVE'!C[-1],'IMPORT'!RC[-20],'OLIVE'!C[6],'IMPORT'!RC[-18],'OLIVE'!C[3],""Traitement/Transfert"",'OLIVE'!C[8],""Tonne"")"
    Range("AN2").Select
    Selection.AutoFill Destination:=Range("AN2:AN" & .Rows(.Rows.Count).Row), Type:=xlFillDefault
    Range("AN2:X" & .Rows(.Rows.Count).Row).Select
'Calcul 12
Range("AP2").Select
    ActiveCell.FormulaR1C1 = _
        "=SUMIFS('OLIVE'!C[7],'OLIVE'!C,'IMPORT'!RC[-23],'OLIVE'!C[1],'IMPORT'!RC[-22],'OLIVE'!C[-8],'IMPORT'!RC[-21],'OLIVE'!C[-1],'IMPORT'!RC[-20],'OLIVE'!C[6],'IMPORT'!RC[-18],'OLIVE'!C[3],""Traitement/Transfert"",'OLIVE'!C[8],""Tonne"")"
    Range("AP2").Select
    Selection.AutoFill Destination:=Range("AP2:AP" & .Rows(.Rows.Count).Row), Type:=xlFillDefault
    Range("AP2:X" & .Rows(.Rows.Count).Row).Select
'Calcul 13
Range("AV2").Select
    ActiveCell.FormulaR1C1 = _
        "=SUMIFS('OLIVE'!C[7],'OLIVE'!C,'IMPORT'!RC[-23],'OLIVE'!C[1],'IMPORT'!RC[-22],'OLIVE'!C[-8],'IMPORT'!RC[-21],'OLIVE'!C[-1],'IMPORT'!RC[-20],'OLIVE'!C[6],'IMPORT'!RC[-18],'OLIVE'!C[3],""Traitement/Transfert"",'OLIVE'!C[8],""Tonne"")"
    Range("AV2").Select
    Selection.AutoFill Destination:=Range("AV2:AV" & .Rows(.Rows.Count).Row), Type:=xlFillDefault
    Range("AV2:X" & .Rows(.Rows.Count).Row).Select
'Calcul 14
Range("AX2").Select
    ActiveCell.FormulaR1C1 = _
        "=SUMIFS('OLIVE'!C[7],'OLIVE'!C,'IMPORT'!RC[-23],'OLIVE'!C[1],'IMPORT'!RC[-22],'OLIVE'!C[-8],'IMPORT'!RC[-21],'OLIVE'!C[-1],'IMPORT'!RC[-20],'OLIVE'!C[6],'IMPORT'!RC[-18],'OLIVE'!C[3],""Traitement/Transfert"",'OLIVE'!C[8],""Tonne"")"
    Range("AX2").Select
    Selection.AutoFill Destination:=Range("AX2:AX" & .Rows(.Rows.Count).Row), Type:=xlFillDefault
    Range("AX2:X" & .Rows(.Rows.Count).Row).Select


'standardise les cellules des formules

    
    Range("J2:J" & .Rows(.Rows.Count).Row).Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
        

     
 ' Standardise les RechercheV 
 
'

Range("E2:E" & .Rows(.Rows.Count).Row).Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    
    Range("G2:G" & .Rows(.Rows.Count).Row).Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    
    Range("I2:I" & .Rows(.Rows.Count).Row).Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    

' doublons2 Macro
'

ActiveSheet.Range("$A$1:$BI$" & .Rows(.Rows.Count).Row).RemoveDuplicates Columns:=Array(1, 2, 3, 5, 7) _
        , Header:=xlYes
    Range("E170").Select
    Sheets("REF FILE").Select
    Range("C12").Select
    ActiveWindow.SmallScroll Down:=144
    Columns("A:A").ColumnWidth = 27.29
    Sheets("IMPORT").Select
    

'Supprime les colonnes RechercheV 

Range("D1").Select
    Selection.Copy
    Range("E1").Select
    ActiveSheet.Paste
    Range("F1").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("G1").Select
    ActiveSheet.Paste
    Columns("D:D").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    Columns("E:E").Select
    Selection.Delete Shift:=xlToLeft
    
    
    
Application.ScreenUpdating = True

End With
    
End Sub

A voir également:

Besoin d'aide pour "nettoyer" mon code VBA EXCEL
Nettoyer mac - Guide
Liste déroulante excel - Guide
Nettoyer ordinateur portable lent - Guide
Nettoyer port usb c - Guide
Formule excel - Guide

2 réponses

Réponse 1 / 2

yg_be Messages postés 22754 Date d'inscription lundi 9 juin 2008 Statut Contributeur Dernière intervention 4 mai 2024 1 479
Modifié le 7 oct. 2018 à 18:33

bonjour, suggestions:

Sheets("IMPORT").Columns("A:A").value = Sheets("OLIVE").Columns("X:X").value

Sheets("OLIVE").Columns("X:X").copy Sheets("IMPORT").Columns("A:A")

Sheets("OLIVE").Range("E2").FormulaR1C1 = _
"=RECHERCHEV('IMPORT'!D2;'REF FILE'!A:B;2;faux)"

With Columns("A:I").Font

Utilisateur anonyme
7 oct. 2018 à 18:38

Merci beaucoup, c'est déjà un bon début !

Si vous trouvez d'autres petites optimisations possibles, je suis preneur !

yg_be Messages postés 22754 Date d'inscription lundi 9 juin 2008 Statut Contributeur Dernière intervention 4 mai 2024 1 479 > Utilisateur anonyme
7 oct. 2018 à 18:46

montre-nous les Select/Activate que tu ne peux pas supprimer.

Utilisateur anonyme
7 oct. 2018 à 18:58

Mon but est de supprimer tous les .SELECT / ACTIVATE de mon code

Exemple:

Columns("E:E").Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

Je ne sais pas comment supprimer le .SELECT ici

et ici:

Range("AX2").Select
Selection.AutoFill Destination:=Range("AX2:AX" & .Rows(.Rows.Count).Row), Type:=xlFillDefault
Range("AX2:X" & .Rows(.Rows.Count).Row).Select

Réponse 2 / 2

yg_be Messages postés 22754 Date d'inscription lundi 9 juin 2008 Statut Contributeur Dernière intervention 4 mai 2024 1 479
7 oct. 2018 à 20:20

as-tu essayé:

Application.CutCopyMode = False
Columns("E:E").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

et :

Range("AX2").AutoFill Destination:=Range("AX2:AX" & .Rows(.Rows.Count).Row), Type:=xlFillDefault

Newsletters