Programme trop lent

Résolu/Fermé
hedi - 17 janv. 2014 à 15:53
hedi - 17 janv. 2014 à 17:24
Bonjour, le programme met actuellement 15min environ pour s'executer, alors qu'il consiste en une mise a jour de peu de cases, mais il doit comparer enormement de cases entre 2 tableaux. Il y a 25 colonnes et 1000 lignes dans le tableau de base. Que faire ?

Voici le code:
Private Sub Workbook_Open()

Application.DisplayAlerts = False
Application.ScreenUpdating = False
Dim titre As String
Dim wbk1 As Workbook
Dim wbk2 As Workbook 'Pr utilisation et mise a jour: activer macros, enregistrer base et relancer
titre = "C:\Users\Lucas\Desktop\CATALOGUE ZYLO COMPLET (4).xlsx"
Set wbk1 = ThisWorkbook
Set wbk2 = Workbooks.Open(titre)
Dim derniere_ligneB As Integer
Dim derniere_ligneR As Integer
derniere_ligneB = wbk2.Sheets(1).Range("C" & Rows.Count).End(xlUp).Row
Dim k As Integer

For i = 1 To derniere_ligneB
derniere_ligneR = wbk1.Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
For j = 1 To derniere_ligneR
If wbk2.Sheets(1).Range("C" & i + 2).Value = wbk1.Sheets(1).Range("A" & j + 1).Value And wbk2.Sheets(1).Range("R" & i + 2).Value <> "NON" And wbk2.Sheets(1).Range("B" & i + 2).Value = "ACHAT" Then wbk1.Sheets(1).Range("A" & j + 1).Value = wbk2.Sheets(1).Range("C" & i + 2).Value
If wbk2.Sheets(1).Range("C" & i + 2).Value = wbk1.Sheets(1).Range("A" & j + 1).Value And wbk2.Sheets(1).Range("R" & i + 2).Value <> "NON" And wbk2.Sheets(1).Range("B" & i + 2).Value = "ACHAT" Then wbk1.Sheets(1).Range("B" & j + 1).Value = wbk2.Sheets(1).Range("D" & i + 2).Value
If wbk2.Sheets(1).Range("C" & i + 2).Value = wbk1.Sheets(1).Range("A" & j + 1).Value And wbk2.Sheets(1).Range("R" & i + 2).Value <> "NON" And wbk2.Sheets(1).Range("B" & i + 2).Value = "ACHAT" Then wbk1.Sheets(1).Range("C" & j + 1).Value = wbk2.Sheets(1).Range("R" & i + 2).Value
If wbk2.Sheets(1).Range("C" & i + 2).Value = wbk1.Sheets(1).Range("A" & j + 1).Value And wbk2.Sheets(1).Range("R" & i + 2).Value <> "NON" And wbk2.Sheets(1).Range("B" & i + 2).Value = "ACHAT" Then wbk1.Sheets(1).Range("D" & j + 1).Value = wbk2.Sheets(1).Range("S" & i + 2).Value
If wbk2.Sheets(1).Range("C" & i + 2).Value = wbk1.Sheets(1).Range("A" & j + 1).Value And wbk2.Sheets(1).Range("R" & i + 2).Value <> "NON" And wbk2.Sheets(1).Range("B" & i + 2).Value = "ACHAT" Then wbk1.Sheets(1).Range("E" & j + 1).Value = wbk2.Sheets(1).Range("BN" & i + 2).Value
If wbk2.Sheets(1).Range("C" & i + 2).Value = wbk1.Sheets(1).Range("A" & j + 1).Value And wbk2.Sheets(1).Range("R" & i + 2).Value <> "NON" And wbk2.Sheets(1).Range("B" & i + 2).Value = "ACHAT" Then wbk1.Sheets(1).Range("F" & j + 1).Value = wbk2.Sheets(1).Range("BO" & i + 2).Value
If wbk2.Sheets(1).Range("C" & i + 2).Value = wbk1.Sheets(1).Range("A" & j + 1).Value And wbk2.Sheets(1).Range("R" & i + 2).Value <> "NON" And wbk2.Sheets(1).Range("B" & i + 2).Value = "ACHAT" Then wbk1.Sheets(1).Range("G" & j + 1).Value = wbk2.Sheets(1).Range("BP" & i + 2).Value
If wbk2.Sheets(1).Range("C" & i + 2).Value = wbk1.Sheets(1).Range("A" & j + 1).Value And wbk2.Sheets(1).Range("R" & i + 2).Value <> "NON" And wbk2.Sheets(1).Range("B" & i + 2).Value = "ACHAT" Then wbk1.Sheets(1).Range("H" & j + 1).Value = wbk2.Sheets(1).Range("BK" & i + 2).Value
If wbk2.Sheets(1).Range("C" & i + 2).Value = wbk1.Sheets(1).Range("A" & j + 1).Value And wbk2.Sheets(1).Range("R" & i + 2).Value <> "NON" And wbk2.Sheets(1).Range("B" & i + 2).Value = "ACHAT" Then wbk1.Sheets(1).Range("I" & j + 1).Value = wbk2.Sheets(1).Range("BJ" & i + 2).Value
If wbk2.Sheets(1).Range("C" & i + 2).Value = wbk1.Sheets(1).Range("A" & j + 1).Value And wbk2.Sheets(1).Range("R" & i + 2).Value <> "NON" And wbk2.Sheets(1).Range("B" & i + 2).Value = "ACHAT" Then wbk1.Sheets(1).Range("J" & j + 1).Value = wbk2.Sheets(1).Range("BI" & i + 2).Value
If wbk2.Sheets(1).Range("C" & i + 2).Value = wbk1.Sheets(1).Range("A" & j + 1).Value And wbk2.Sheets(1).Range("R" & i + 2).Value <> "NON" And wbk2.Sheets(1).Range("B" & i + 2).Value = "ACHAT" Then wbk1.Sheets(1).Range("K" & j + 1).Value = wbk2.Sheets(1).Range("BE" & i + 2).Value
If wbk2.Sheets(1).Range("C" & i + 2).Value = wbk1.Sheets(1).Range("A" & j + 1).Value And wbk2.Sheets(1).Range("R" & i + 2).Value <> "NON" And wbk2.Sheets(1).Range("B" & i + 2).Value = "ACHAT" Then wbk1.Sheets(1).Range("L" & j + 1).Value = wbk2.Sheets(1).Range("BG" & i + 2).Value
If wbk2.Sheets(1).Range("C" & i + 2).Value = wbk1.Sheets(1).Range("A" & j + 1).Value And wbk2.Sheets(1).Range("R" & i + 2).Value <> "NON" And wbk2.Sheets(1).Range("B" & i + 2).Value = "ACHAT" Then wbk1.Sheets(1).Range("M" & j + 1).Value = wbk2.Sheets(1).Range("BH" & i + 2).Value
If wbk2.Sheets(1).Range("C" & i + 2).Value = wbk1.Sheets(1).Range("A" & j + 1).Value And wbk2.Sheets(1).Range("R" & i + 2).Value <> "NON" And wbk2.Sheets(1).Range("B" & i + 2).Value = "ACHAT" Then wbk1.Sheets(1).Range("N" & j + 1).Value = wbk2.Sheets(1).Range("BF" & i + 2).Value
If wbk2.Sheets(1).Range("C" & i + 2).Value = wbk1.Sheets(1).Range("A" & j + 1).Value And wbk2.Sheets(1).Range("R" & i + 2).Value <> "NON" And wbk2.Sheets(1).Range("B" & i + 2).Value = "ACHAT" Then wbk1.Sheets(1).Range("O" & j + 1).Value = wbk2.Sheets(1).Range("G" & i + 2).Value
If wbk2.Sheets(1).Range("C" & i + 2).Value = wbk1.Sheets(1).Range("A" & j + 1).Value And wbk2.Sheets(1).Range("R" & i + 2).Value <> "NON" And wbk2.Sheets(1).Range("B" & i + 2).Value = "ACHAT" Then wbk1.Sheets(1).Range("P" & j + 1).Value = wbk2.Sheets(1).Range("H" & i + 2).Value
If wbk2.Sheets(1).Range("C" & i + 2).Value = wbk1.Sheets(1).Range("A" & j + 1).Value And wbk2.Sheets(1).Range("R" & i + 2).Value <> "NON" And wbk2.Sheets(1).Range("B" & i + 2).Value = "ACHAT" Then wbk1.Sheets(1).Range("Q" & j + 1).Value = wbk2.Sheets(1).Range("I" & i + 2).Value
If wbk2.Sheets(1).Range("C" & i + 2).Value = wbk1.Sheets(1).Range("A" & j + 1).Value And wbk2.Sheets(1).Range("R" & i + 2).Value <> "NON" And wbk2.Sheets(1).Range("B" & i + 2).Value = "ACHAT" Then wbk1.Sheets(1).Range("R" & j + 1).Value = wbk2.Sheets(1).Range("J" & i + 2).Value
If wbk2.Sheets(1).Range("C" & i + 2).Value = wbk1.Sheets(1).Range("A" & j + 1).Value And wbk2.Sheets(1).Range("R" & i + 2).Value <> "NON" And wbk2.Sheets(1).Range("B" & i + 2).Value = "ACHAT" Then wbk1.Sheets(1).Range("S" & j + 1).Value = wbk2.Sheets(1).Range("K" & i + 2).Value
If wbk2.Sheets(1).Range("C" & i + 2).Value = wbk1.Sheets(1).Range("A" & j + 1).Value And wbk2.Sheets(1).Range("R" & i + 2).Value <> "NON" And wbk2.Sheets(1).Range("B" & i + 2).Value = "ACHAT" Then wbk1.Sheets(1).Range("T" & j + 1).Value = wbk2.Sheets(1).Range("BQ" & i + 2).Value
If wbk2.Sheets(1).Range("C" & i + 2).Value = wbk1.Sheets(1).Range("A" & j + 1).Value And wbk2.Sheets(1).Range("R" & i + 2).Value <> "NON" And wbk2.Sheets(1).Range("B" & i + 2).Value = "ACHAT" Then wbk1.Sheets(1).Range("U" & j + 1).Value = wbk2.Sheets(1).Range("BR" & i + 2).Value
If wbk2.Sheets(1).Range("C" & i + 2).Value = wbk1.Sheets(1).Range("A" & j + 1).Value And wbk2.Sheets(1).Range("R" & i + 2).Value <> "NON" And wbk2.Sheets(1).Range("B" & i + 2).Value = "ACHAT" Then wbk1.Sheets(1).Range("V" & j + 1).Value = wbk2.Sheets(1).Range("BS" & i + 2).Value
If wbk2.Sheets(1).Range("C" & i + 2).Value = wbk1.Sheets(1).Range("A" & j + 1).Value And wbk2.Sheets(1).Range("R" & i + 2).Value <> "NON" And wbk2.Sheets(1).Range("B" & i + 2).Value = "ACHAT" Then wbk1.Sheets(1).Range("W" & j + 1).Value = wbk2.Sheets(1).Range("BT" & i + 2).Value
If wbk2.Sheets(1).Range("C" & i + 2).Value = wbk1.Sheets(1).Range("A" & j + 1).Value And wbk2.Sheets(1).Range("R" & i + 2).Value <> "NON" And wbk2.Sheets(1).Range("B" & i + 2).Value = "ACHAT" Then wbk1.Sheets(1).Range("X" & j + 1).Value = wbk2.Sheets(1).Range("CA" & i + 2).Value
If wbk2.Sheets(1).Range("C" & i + 2).Value = wbk1.Sheets(1).Range("A" & j + 1).Value And wbk2.Sheets(1).Range("R" & i + 2).Value <> "NON" And wbk2.Sheets(1).Range("B" & i + 2).Value = "ACHAT" Then wbk1.Sheets(1).Range("AA" & j + 1).Value = wbk2.Sheets(1).Range("BU" & i + 2).Value
If wbk2.Sheets(1).Range("C" & i + 2).Value = wbk1.Sheets(1).Range("A" & j + 1).Value And wbk2.Sheets(1).Range("R" & i + 2).Value <> "NON" And wbk2.Sheets(1).Range("B" & i + 2).Value = "ACHAT" Then wbk1.Sheets(1).Range("AB" & j + 1).Value = wbk2.Sheets(1).Range("BW" & i + 2).Value
If wbk2.Sheets(1).Range("C" & i + 2).Value = wbk1.Sheets(1).Range("A" & j + 1).Value And wbk2.Sheets(1).Range("R" & i + 2).Value <> "NON" And wbk2.Sheets(1).Range("B" & i + 2).Value = "ACHAT" Then wbk1.Sheets(1).Range("AC" & j + 1).Value = wbk2.Sheets(1).Range("BY" & i + 2).Value
If wbk2.Sheets(1).Range("C" & i + 2).Value = wbk1.Sheets(1).Range("A" & j + 1).Value And wbk2.Sheets(1).Range("R" & i + 2).Value <> "NON" And wbk2.Sheets(1).Range("B" & i + 2).Value = "ACHAT" Then wbk1.Sheets(1).Range("AD" & j + 1).Value = wbk2.Sheets(1).Range("BZ" & i + 2).Value



Next
Next

For i = 1 To derniere_ligneB
derniere_ligneR = wbk1.Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
k = 0
For j = 1 To derniere_ligneR
If wbk2.Sheets(1).Range("C" & i + 2).Value <> wbk1.Sheets(1).Range("A" & j + 1).Value Then k = k + 1
Next

If k = derniere_ligneR And wbk2.Sheets(1).Range("R" & i + 2).Value <> "NON" And wbk2.Sheets(1).Range("B" & i + 2).Value = "ACHAT" Then wbk1.Sheets(1).Range("A" & derniere_ligneR + 1).Value = wbk2.Sheets(1).Range("C" & i + 2).Value
If k = derniere_ligneR And wbk2.Sheets(1).Range("R" & i + 2).Value <> "NON" And wbk2.Sheets(1).Range("B" & i + 2).Value = "ACHAT" Then wbk1.Sheets(1).Range("B" & derniere_ligneR + 1).Value = wbk2.Sheets(1).Range("D" & i + 2).Value
If k = derniere_ligneR And wbk2.Sheets(1).Range("R" & i + 2).Value <> "NON" And wbk2.Sheets(1).Range("B" & i + 2).Value = "ACHAT" Then wbk1.Sheets(1).Range("C" & derniere_ligneR + 1).Value = wbk2.Sheets(1).Range("R" & i + 2).Value
If k = derniere_ligneR And wbk2.Sheets(1).Range("R" & i + 2).Value <> "NON" And wbk2.Sheets(1).Range("B" & i + 2).Value = "ACHAT" Then wbk1.Sheets(1).Range("D" & derniere_ligneR + 1).Value = wbk2.Sheets(1).Range("S" & i + 2).Value
If k = derniere_ligneR And wbk2.Sheets(1).Range("R" & i + 2).Value <> "NON" And wbk2.Sheets(1).Range("B" & i + 2).Value = "ACHAT" Then wbk1.Sheets(1).Range("E" & derniere_ligneR + 1).Value = wbk2.Sheets(1).Range("BN" & i + 2).Value
If k = derniere_ligneR And wbk2.Sheets(1).Range("R" & i + 2).Value <> "NON" And wbk2.Sheets(1).Range("B" & i + 2).Value = "ACHAT" Then wbk1.Sheets(1).Range("F" & derniere_ligneR + 1).Value = wbk2.Sheets(1).Range("BO" & i + 2).Value
If k = derniere_ligneR And wbk2.Sheets(1).Range("R" & i + 2).Value <> "NON" And wbk2.Sheets(1).Range("B" & i + 2).Value = "ACHAT" Then wbk1.Sheets(1).Range("G" & derniere_ligneR + 1).Value = wbk2.Sheets(1).Range("BP" & i + 2).Value
If k = derniere_ligneR And wbk2.Sheets(1).Range("R" & i + 2).Value <> "NON" And wbk2.Sheets(1).Range("B" & i + 2).Value = "ACHAT" Then wbk1.Sheets(1).Range("H" & derniere_ligneR + 1).Value = wbk2.Sheets(1).Range("BK" & i + 2).Value
If k = derniere_ligneR And wbk2.Sheets(1).Range("R" & i + 2).Value <> "NON" And wbk2.Sheets(1).Range("B" & i + 2).Value = "ACHAT" Then wbk1.Sheets(1).Range("I" & derniere_ligneR + 1).Value = wbk2.Sheets(1).Range("BJ" & i + 2).Value
If k = derniere_ligneR And wbk2.Sheets(1).Range("R" & i + 2).Value <> "NON" And wbk2.Sheets(1).Range("B" & i + 2).Value = "ACHAT" Then wbk1.Sheets(1).Range("J" & derniere_ligneR + 1).Value = wbk2.Sheets(1).Range("BI" & i + 2).Value
If k = derniere_ligneR And wbk2.Sheets(1).Range("R" & i + 2).Value <> "NON" And wbk2.Sheets(1).Range("B" & i + 2).Value = "ACHAT" Then wbk1.Sheets(1).Range("K" & derniere_ligneR + 1).Value = wbk2.Sheets(1).Range("BE" & i + 2).Value
If k = derniere_ligneR And wbk2.Sheets(1).Range("R" & i + 2).Value <> "NON" And wbk2.Sheets(1).Range("B" & i + 2).Value = "ACHAT" Then wbk1.Sheets(1).Range("L" & derniere_ligneR + 1).Value = wbk2.Sheets(1).Range("BG" & i + 2).Value
If k = derniere_ligneR And wbk2.Sheets(1).Range("R" & i + 2).Value <> "NON" And wbk2.Sheets(1).Range("B" & i + 2).Value = "ACHAT" Then wbk1.Sheets(1).Range("M" & derniere_ligneR + 1).Value = wbk2.Sheets(1).Range("BH" & i + 2).Value
If k = derniere_ligneR And wbk2.Sheets(1).Range("R" & i + 2).Value <> "NON" And wbk2.Sheets(1).Range("B" & i + 2).Value = "ACHAT" Then wbk1.Sheets(1).Range("N" & derniere_ligneR + 1).Value = wbk2.Sheets(1).Range("BF" & i + 2).Value
If k = derniere_ligneR And wbk2.Sheets(1).Range("R" & i + 2).Value <> "NON" And wbk2.Sheets(1).Range("B" & i + 2).Value = "ACHAT" Then wbk1.Sheets(1).Range("O" & derniere_ligneR + 1).Value = wbk2.Sheets(1).Range("G" & i + 2).Value
If k = derniere_ligneR And wbk2.Sheets(1).Range("R" & i + 2).Value <> "NON" And wbk2.Sheets(1).Range("B" & i + 2).Value = "ACHAT" Then wbk1.Sheets(1).Range("P" & derniere_ligneR + 1).Value = wbk2.Sheets(1).Range("H" & i + 2).Value
If k = derniere_ligneR And wbk2.Sheets(1).Range("R" & i + 2).Value <> "NON" And wbk2.Sheets(1).Range("B" & i + 2).Value = "ACHAT" Then wbk1.Sheets(1).Range("Q" & derniere_ligneR + 1).Value = wbk2.Sheets(1).Range("I" & i + 2).Value
If k = derniere_ligneR And wbk2.Sheets(1).Range("R" & i + 2).Value <> "NON" And wbk2.Sheets(1).Range("B" & i + 2).Value = "ACHAT" Then wbk1.Sheets(1).Range("R" & derniere_ligneR + 1).Value = wbk2.Sheets(1).Range("J" & i + 2).Value
If k = derniere_ligneR And wbk2.Sheets(1).Range("R" & i + 2).Value <> "NON" And wbk2.Sheets(1).Range("B" & i + 2).Value = "ACHAT" Then wbk1.Sheets(1).Range("S" & derniere_ligneR + 1).Value = wbk2.Sheets(1).Range("K" & i + 2).Value
If k = derniere_ligneR And wbk2.Sheets(1).Range("R" & i + 2).Value <> "NON" And wbk2.Sheets(1).Range("B" & i + 2).Value = "ACHAT" Then wbk1.Sheets(1).Range("T" & derniere_ligneR + 1).Value = wbk2.Sheets(1).Range("BQ" & i + 2).Value
If k = derniere_ligneR And wbk2.Sheets(1).Range("R" & i + 2).Value <> "NON" And wbk2.Sheets(1).Range("B" & i + 2).Value = "ACHAT" Then wbk1.Sheets(1).Range("U" & derniere_ligneR + 1).Value = wbk2.Sheets(1).Range("BR" & i + 2).Value
If k = derniere_ligneR And wbk2.Sheets(1).Range("R" & i + 2).Value <> "NON" And wbk2.Sheets(1).Range("B" & i + 2).Value = "ACHAT" Then wbk1.Sheets(1).Range("V" & derniere_ligneR + 1).Value = wbk2.Sheets(1).Range("BS" & i + 2).Value
If k = derniere_ligneR And wbk2.Sheets(1).Range("R" & i + 2).Value <> "NON" And wbk2.Sheets(1).Range("B" & i + 2).Value = "ACHAT" Then wbk1.Sheets(1).Range("W" & derniere_ligneR + 1).Value = wbk2.Sheets(1).Range("BT" & i + 2).Value
If k = derniere_ligneR And wbk2.Sheets(1).Range("R" & i + 2).Value <> "NON" And wbk2.Sheets(1).Range("B" & i + 2).Value = "ACHAT" Then wbk1.Sheets(1).Range("X" & derniere_ligneR + 1).Value = wbk2.Sheets(1).Range("CA" & i + 2).Value
If k = derniere_ligneR And wbk2.Sheets(1).Range("R" & i + 2).Value <> "NON" And wbk2.Sheets(1).Range("B" & i + 2).Value = "ACHAT" Then wbk1.Sheets(1).Range("AA" & derniere_ligneR + 1).Value = wbk2.Sheets(1).Range("BU" & i + 2).Value
If k = derniere_ligneR And wbk2.Sheets(1).Range("R" & i + 2).Value <> "NON" And wbk2.Sheets(1).Range("B" & i + 2).Value = "ACHAT" Then wbk1.Sheets(1).Range("AB" & derniere_ligneR + 1).Value = wbk2.Sheets(1).Range("BW" & i + 2).Value
If k = derniere_ligneR And wbk2.Sheets(1).Range("R" & i + 2).Value <> "NON" And wbk2.Sheets(1).Range("B" & i + 2).Value = "ACHAT" Then wbk1.Sheets(1).Range("AC" & derniere_ligneR + 1).Value = wbk2.Sheets(1).Range("BY" & i + 2).Value
If k = derniere_ligneR And wbk2.Sheets(1).Range("R" & i + 2).Value <> "NON" And wbk2.Sheets(1).Range("B" & i + 2).Value = "ACHAT" Then wbk1.Sheets(1).Range("AD" & derniere_ligneR + 1).Value = wbk2.Sheets(1).Range("BZ" & i + 2).Value
Next

wbk2.Close

End Sub
Merci !

Hedi
A voir également:
Programme trop lent
Ordinateur lent - Guide
Programme demarrage windows 10 - Guide
Mon mac est lent comment le nettoyer - Guide
Programme démarrage windows 10 - Guide
Ce message d'erreur apparaît sur l'ordinateur d'elena. que peut-on dire du programme gwxux ? - Forum logiciel systeme
1 réponse

Réponse 1 / 1
hedi
17 janv. 2014 à 17:24
Probleme resolu merci. J'ai fait un If avec plusieurs conséquences au lieu de 25 if.

Aurevoir
Discussions similaires

programme qui n'affiche pas le mot secret
Pourquoi Program files (x86) et Programmes?
"BONJOUR" : utilité de ce programme???
TV LG affiche le carré non programmé
clavier subitement lent !
Discussions similaires

Newsletters
