Menu

Besoin d'aide pour "nettoyer" mon code VBA EXCEL [Résolu]

daxzapui 5 Messages postés mardi 25 septembre 2018Date d'inscription 7 octobre 2018 Dernière intervention - 7 oct. 2018 à 18:10 - Dernière réponse : yg_be 6679 Messages postés lundi 9 juin 2008Date d'inscriptionContributeurStatut 18 octobre 2018 Dernière intervention
- 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





Afficher la suite 

Votre réponse

5 réponses

yg_be 6679 Messages postés lundi 9 juin 2008Date d'inscriptionContributeurStatut 18 octobre 2018 Dernière intervention - Modifié par yg_be le 7/10/2018 à 18:33
0
Merci
bonjour, suggestions:
Sheets("IMPORT").Columns("A:A").value = Sheets("OLIVE").Columns("X:X").value

ou
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
daxzapui 5 Messages postés mardi 25 septembre 2018Date d'inscription 7 octobre 2018 Dernière intervention - 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 6679 Messages postés lundi 9 juin 2008Date d'inscriptionContributeurStatut 18 octobre 2018 Dernière intervention > daxzapui 5 Messages postés mardi 25 septembre 2018Date d'inscription 7 octobre 2018 Dernière intervention - 7 oct. 2018 à 18:46
montre-nous les Select/Activate que tu ne peux pas supprimer.
daxzapui 5 Messages postés mardi 25 septembre 2018Date d'inscription 7 octobre 2018 Dernière intervention - 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
Commenter la réponse de yg_be
yg_be 6679 Messages postés lundi 9 juin 2008Date d'inscriptionContributeurStatut 18 octobre 2018 Dernière intervention - 7 oct. 2018 à 20:20
0
Merci
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
Commenter la réponse de yg_be