Enregistrer données dans un tableau / optimiser traitement boucle

Le 05/02/2020 à 16:21

Bonjour à tous,

Je travaille sur une base de données avec plus de 100 000 lignes dans laquelle j'ai (grâce à l'aide de ce forum) écrit une macro qui fait :

Si la colonne G contient les mots MAGASIN, MAG, MAG., MA ou ECHANTILLONS alors inscrire "NON" en colonne A sinon inscrire "OUI".

Voici mon code :

Option Explicit

Sub Test_fonctions()

Application.ScreenUpdating = False

Worksheets("SO Net Volume FRANCE pour Excel").Activate

  '*  DECLARATIONS DES VARIABLES  *
  Dim i As Long, Dl As Long
  Dim Ws As Worksheet
  '* INITIALISATION DES VARIABLES *
  Set Ws = Sheets("SO Net Volume FRANCE pour Excel")
  Dl = Ws.Range("G" & Rows.Count).End(xlUp).Row 'Derniere ligne remplie
  For i = 2 To Dl 'boucler de la ligne 2 à la derniere de la colonne G
  ' si la cellule contient (like) MAGASIN, MAG, MAG., MA ou ECHANTILLONS
    If Ws.Cells(i, "G") Like "*MAGASIN*" Or Ws.Cells(i, "G") Like "*MAG*" Or Ws.Cells(i, "G") Like "*MAG.*" Or Ws.Cells(i, "G") Like "*MA*" Or Ws.Cells(i, "G") Like "*ECHANTILLONS*" Then
      Ws.Cells(i, "A") = "Non" 'Je met non en A
    Else
      Ws.Cells(i, "A") = "Oui" ' Sinon je met oui
    End If
  Next i

Application.ScreenUpdating = True
End Sub

Comme vous pouvez le voir, j'utilise une boucle For mais le problème c'est que le temps de traitement est hyper long/ voir, fait planter Excel....

J'ai pu lire qu'il fallait plutôt travailler sur un tableau lorsque que l'on a beaucoup de lignes à traiter;

Pourriez vous m'aider s'il vous plaît à déclarer un tableau dans lequel j'enregistre les données de ma base (qui sont variables), puis exécuter ma boucle For sur ce tableau ?

J'espère avoir été suffisamment claire et vous remercie par avance pour votre aide précieuse!

Je ne peux malheuresement pas joindre de fichier car il est beaucoup trop gros, j'espère qu'avec le code au-dessus cela suffira !

Merci !

Victoire

Le 05/02/2020 à 16:55

Salut Victoire,

à tester...

Public Sub G_MAG_To_A_OUI()
'
Dim tTab
'
With Worksheets("SO Net Volume FRANCE pour Excel")
    tTab = .Range("A2:G" & .Range("G" & Rows.Count).End(xlUp).Row).Value
    For x = 1 To UBound(tTab, 1)
        tTab(x, 1) = IIf(InStr(tTab(x, 7), "MA") > 0 Or InStr(tTab(x, 7), "ECHANTILLONS") > 0, "Oui", "Non")
    Next
End With
.Range("A2").Resize(UBound(tTab, 1), 7).Value = tTab
'
End Sub

A+

Le 06/02/2020 à 08:39

Bonjour curulis57,

Merci pour ton rapide retour !

Je suis en plein apprentissage VBA, pourrait tu m'expliquer quelques parties de ton code pour que je puisse parfaitement le comprendre et le ré-appliquer plus aisément ?

J'ai essayé de rajouter des commentaires à ton code pour mieux le comprendre, peux tu me dire si c'est bien cela s'il te plaît et à défaut me corriger ?

Public Sub G_MAG_To_A_OUI()
'
Dim tTab
'
With Worksheets("SO Net Volume FRANCE pour Excel")
    'je déclare mon tableau de taille A2:G+ le nombre de ligne jusqu'en bas
    tTab = .Range("A2:G" & .Range("G" & Rows.Count).End(xlUp).Row).Value

    'J'utilise un indice allant de 1 à la plus grande valeur d'indice disponible pour mon tableau tTab a une dimension
    For x = 1 To UBound(tTab, 1)

        'Je teste la position de la première occurrence de "MA" dans mon tableau à 7 colonnes
        tTab(x, 1) = IIf(InStr(tTab(x, 7), "MA") > 0 Or InStr(tTab(x, 7), "ECHANTILLONS") > 0, "Oui", "Non")
    Next
End With

'J'attribue la valeur "oui"/"non" dans la cellule correspondante en colonne A ?
.Range("A2").Resize(UBound(tTab, 1), 7).Value = tTab
'
End Sub

Merci par avance pour ton aide !

Victoire

Le 06/02/2020 à 13:31

Salut Victoire,

Public Sub G_MAG_To_A_OUI()
'
Dim tTab
'
With Worksheets("SO Net Volume FRANCE pour Excel")
    'j'initialise mon tableau de taille A2:G+ le nombre de ligne jusqu'en bas
    tTab = .Range("A2:G" & .Range("G" & Rows.Count).End(xlUp).Row).Value

    'J'utilise un indice allant de 1 à la plus grande valeur d'indice disponible pour mon tableau tTab a deux dimensions
    'UBound(tTab, 1) = lignes
    'UBound(tTab, 2) = colonnes
    For x = 1 To UBound(tTab, 1)
        'tTab(x, 1) = Range("A" & x).value
        'tTab(x, 7) = Range("G" & x).value
        '"Ax" = si (Iif)  je trouve (Instr) "MA" ou (Or) "ECHANTILLONS" dans "Gx" , Non sinon = Oui
        tTab(x, 1) = IIf(InStr(tTab(x, 7), "MA") > 0 Or InStr(tTab(x, 7), "ECHANTILLONS") > 0, "Non", "Oui")
    Next
End With

'tTab modifié est affiché en .Range("A2:G +" aux dimensins initiales de son initialisation (Resize)
.Range("A2").Resize(UBound(tTab, 1), 7).Value = tTab
'
End Sub

A+

Le 06/02/2020 à 13:53

Re bonjour curulis57,

Merci pour tes commentaires sur le code ! Je souhaiterais développer cette procédure : sur la même idée, j'aimerais "tester" deux autres colonnes, dont les réponses "oui" "non" seront également retransmises dans deux autres colonnes de ce même tableau.

J'ai essayer en rajoutant à ton code mon deuxième test : Vérification volume <50kg

Option Explicit

Sub Test_Fonctions()

'Vérification Entrepôt
Dim Tableau
Dim x As Long

With Worksheets("SO Net Volume FRANCE pour Excel")
    Tableau = .Range("A2:S" & .Range("S" & Rows.Count).End(xlUp).Row).Value

    For x = 1 To UBound(Tableau, 1)
        Tableau(x, 1) = IIf(InStr(Tableau(x, 7), "MAGASIN") > 0 Or InStr(Tableau(x, 7), " MAG") > 0 Or InStr(Tableau(x, 7), " MAG.") > 0 Or InStr(Tableau(x, 7), " MA") > 0 Or InStr(Tableau(x, 7), "ECHANTILLONS") > 0, "NON", "OUI")
    Next

End With
Worksheets("SO Net Volume FRANCE pour Excel").Range("A2").Resize(UBound(Tableau, 1), 19).Value = Tableau

'Vérification volume <50kg
With Worksheets("SO Net Volume FRANCE pour Excel")
    Tableau = .Range("A2:S" & .Range("S" & Rows.Count).End(xlUp).Row).Value

    For x = 1 To UBound(Tableau, 1)
        Tableau(x, 1) = IIf(Tableau(x, 19) > 50, "NON", "OUI")
    Next

End With
Worksheets("SO Net Volume FRANCE pour Excel").Range("B2").Resize(UBound(Tableau, 1), 19).Value = Tableau

End Sub

Le problème avec ce code : lorsque je vérifie si mon volume est <50kg et que je resize ma colonne B pour y affecter les valeurs trouvées dans ma boucle, j'ai certes bien les valeurs "oui"/"non" qui se mettent dans ma colonne B, mais les données du reste de mon tableau excel se décalent d'une colonne sur la droite : mes en-têtes ne se décalent pas (puisque je commence mes range à 2) mais les données à partir la ligne 2 se décalent une colonne à droite...

Aussi, peut-être est il possible de faires ces trois tests dans une seule boucle ?

Merci encore pour ton retour !

Victoire

Le 06/02/2020 à 14:56

Salut Victoire,

décalage normal puisque tu colles en [B2] ce que tu as initialisé en [A2] !!

L'instruction d'affichage des résultats devait, évidemment, être dans le bloc WITH...END WITH... 

Public Sub G_MAG_To_A_OUI()
'
Dim tTab
'
With Worksheets("SO Net Volume FRANCE pour Excel")
    'j'initialise mon tableau de taille A2:S+ le nombre de ligne jusqu'en bas
    tTab = .Range("A2:S" & .Range("S" & Rows.Count).End(xlUp).Row).Value
    'J'utilise un indice allant de 1 à la plus grande valeur d'indice-lignes de mon tableau tTab à deux dimensions
    'UBound(tTab, 1) = nre de lignes
    'UBound(tTab, 2) = nbre de colonnes
    For x = 1 To UBound(tTab, 1)
        'tTab(x, 1) = Range("A" & x).value  /   tTab(x, 2) = Range("B" & x).value
        'tTab(x, 7) = Range("G" & x).value  /   tTab(x, 19) = Range("S" & x).value
        '"Ax" = si (Iif)  je trouve (Instr) "MA" ou (Or) "ECHANTILLONS" dans "Gx" , = Non sinon = Oui
        tTab(x, 1) = IIf(InStr(tTab(x, 7), "MA") > 0 Or InStr(tTab(x, 7), "ECHANTILLONS") > 0, "NON", "OUI")
        '"Bx" = si (Iif) "Sx" > 50 , = Non sinon = Oui
        'tTab contenant des valeurs VARIANT, je convertis tTab(x,19) en INTEGER, histoire d'être certain de la comparaison
        tTab(x, 2) = IIf(CInt(Tableau(x, 19)) > 50, "NON", "OUI")
    Next
    'tTab modifié est affiché en .Range("A2:S +" aux dimensins initiales de son initialisation (Resize)
    .Range("A2").Resize(UBound(tTab, 1), 19).Value = tTab
    'on peut aussi écrire, sans erreur ni calculs savants...
    '.Range("A2").Resize(UBound(tTab, 1), UBound(tTab, 2)).Value = tTab
End With
'
End Sub

A+

Le 06/02/2020 à 17:04

Merci beaucoup curulis57!

Haha et oui j'essaye des trucs pas toujours juste ^^' merci pour tes commentaires !

J'ai simplement eu "l'erreur d'exécution '6' : dépassement de capacité" sur la ligne :

'tTab contenant des valeurs VARIANT, je convertis tTab(x,19) en INTEGER, histoire d'être certain de la comparaison
        tTab(x, 2) = IIf(CInt(Tableau(x, 19)) > 50, "NON", "OUI")

et j'ai du coup écris comme ceci et ça marche :

[codetTab(x, 2) = IIf(CLng(tTab(x, 19)) > 50, "NON", "OUI")[/code]

Je t'avoue que j'ai pas forcément compris pourquoi il fallait convertir les valeurs de tTab pour être certain de la comparaison... on les a pas converties pour faire le premier test :

tTab(x, 1) = IIf(InStr(tTab(x, 7), "MA") > 0 Or InStr(tTab(x, 7), "ECHANTILLONS") > 0, "NON", "OUI")

Merci beaucoup

Victoire

Le 06/02/2020 à 17:20

Salut Victoire,

  • je ne convertis pas des caractères alphanumériques : rarement nécessaire ;
  • dans un tableau de ce genre peuvent se retrouver des dates ou des nombres et, là, pour être certain de comparer des pommes entre elles et non des pommes et des poires, je force la conversion : CInt, CDate, CDbl...
  • déso pour le
  • par contre CLng équivaut à tuer un moustique avec une bombe atomique ! CInt suffit largement.

Bonne continuation en VBA !

A+

Le 07/02/2020 à 08:50

Bonjour curulis57,

Merci pour ton retour et tes explications claires !

En convertissant avec CInt, j'ai eu l'erreur d'exécution '6' : dépassement de capacité... en effet mon tableau fais plus de 100 000 lignes c'est pourquoi je me suis tournée vers une concersion "Long".... Mais en effet peut être qu'il existe une conversion "entre-deux" ?

Merci pour tes encouragements, je dois dire que c'est très satisfaisant de voir des codes qui marche !

Victoire

Rechercher des sujets similaires à "enregistrer donnees tableau optimiser traitement boucle"