Discussion :

[barbeaua]

Bonjour à tous et à toutes.

J'ai un grand problème avec mon code VBA, qui se lance lors de la création d'une feuille Excel.
La variable "resultat" est entrée par l'utilisateur à la création d'une feuille qui prend sa valeur comme nom.

Je voudrais que les valeurs d'une colonne de cette feuille (la C) soient copiées dans ma feuille "Bon de commande".
De même avec les valeurs venant d'un autre fichier.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
 With Sheets("Bon de commande")
For i = 5 To 1677
 
    .Cells(i - 2, j - 5).FormulaLocal = "= " & Sheets(resultat).Name & "!C" & i
    .Cells(i - 2, j - 4).FormulaLocal = "='\\Chemindufichier\[Fichier_de_base.xlsx]BPU'!G" & i
    .Cells(i - 2, j - 3).FormulaLocal = "='\\Chemindufichier\[Fichier_de_base.xlsx]BPU'!H" & i
    .Cells(i - 2, j - 2).FormulaLocal = "='\\Chemindufichier\[Fichier_de_base.xlsx]BPU'!I" & i
 
    Next i
End with

Une grande partie de ces valeurs resteront vides, mais je ne trouve pas de solution autre que celle-ci car la feuille "resultat" et "fichier de base" seront modifiés plus tard par l'utilisateur et ces modifications doivent être prises en compte...

Ma boucle est beaucoup trop longue et je ne trouve pas de moyen d'optimisation. J'espère avoir été clair dans mon explication.

En espérant que vous pourrez m'aider!

Cordialement,

Antoine

[kolodz]

Le problème c'est la condition de fin de ta boucle for To 1677. Celle-ci est arbitraire... et bien trop grande sans raison valable.
Il est possible d'obtenir la dernière cellule non vide via : Range.End, propriété (Excel)
Sans faire de VBA moi-même, je pense que la solution est de ce côté. En ayant la boucle for limité à ce qu'il y a vraiment à copier tu va réduire le temps de traitement.

Cordialement,
Patrick Kolodziejczyk.

[Al__22]

Déjà je suis d'accord avec kolodz et ensuite cela doit venir aussi du fait que à chaque fois tu mets dans tes cases une formule d'appel de données venant d'un autre fichier... Donc ton excel doit ouvrir ce fichier en cache chercher les données voulue puis copié celle-ci.
Ce genre de cas peut ne pas être gênant quand c'est dans une ou deux cases mais vu le nombre de fois ou tu l'as met ton fichier doit être saturé
Pourquoi ne pas faire simplement un copié/collé des données voulues sans une formule d'appel ?

[barbeaua]

Bonjour Patrick,

Merci pour ta réponse.

Le problème auquel je fais face, c'est que l'utilisateur va aller modifier le "fichier de base" après.. Et ce fichier contient 1677 lignes.

Par conséquent, je ne peux pas prévoir à l'avance quelles lignes seront vides ou non...

Cordialement,

Antoine

[jeanmidudu]

Bonjour,

Déterminer la dernière ligne remplie :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

.Range(.Cells(Rows.Count, j),.Cells(Rows.count, j)).End(xlUp).row

et
Ainsi tu es certain de ne pas faire de traitement inutiles

Ensuite, ça ne serait pas plus simple de faire seulement une instruction? Un simple copié-collé de l'ensemble de ce qu'il te faut?

[casefayere]

Bonjour,

et si tu montrais le code complet, que représente "j" ?

[Philippe Tulliez]

Bonjour,

Ma boucle est beaucoup trop longue et je ne trouve pas de moyen d'optimisation. J'espère avoir été clair dans mon explication.

D'après la lecture du bout de code, tu copies une formule de la ligne 5 à la ligne 1677 et ce sans condition.
Dans ce cas pourquoi une boucle alors que tu peux écrire en une seule ligne sur la plage complète
Exemple d'une formule (addition) écrite sur une colonne

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Range("C2:C1000").Formula = "=A2+B2"

Cette ligne évite une boucle de 2 à 1000

[Bragu Demon]

Bonjour,

Je pense que kolodz et Al__22 apporte des pistes très intéressante.
Dans le bout de code que tu donnes ce qui est long c'est l'ouverture en arrière plan du classeur qui est sur un emplacement réseau...
Si quand tu souhaites ouvrir ce fichier toi même ça te prends 0,5 seconde ça te prends 14 minutes pour faires les 1167 ouvertures ...

A mon sens il faudrait aller lire le moins souvent ton [Fichier_de_base.xlsx] en mettant tout son contenu dans un dictionnaire puis en le refermant.
Travailler sur ton fichier de bon de commande


Je me suis fait cette fonction pour ça :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
'Remplir un dictionnaire avec le contenu d'une feuille
'@PARAM ligne_debut {Integer} Première ligne de la feuille
'@PARAM colonne_debut {Integer} Première colonne de la feuille
'@PARAM ligne_fin {Integer} Dernière ligne de la feuille
'@PARAM colonne_fin {Integer} Dernière colonne de feuille
'@RETURN {Dictionnary} tableau rempli
Function remplir_dictionnaire( _
    ligne_debut As Integer, _
    colonne_debut As Integer, _
    ligne_fin As Integer, _
    colonne_fin As Integer _
    ) As Dictionary
Dim dic As New Dictionary
Dim item As String
  i = 0
  j = 0
  k = 0
    For i = ligne_debut To ligne_fin
      For j = colonne_debut To colonne_fin
        If j = 1 Then
          item = Cells(i, j).value
        Else
          item = item & ";" & Cells(i, j).value
        End If
      Next j
      dic.add k, item
      k = k + 1
    Next i
  Set remplir_dictionnaire = dic
  Set dic = Nothing
End Function

J'appelle cette fonction comme ci après (tout ce trouve dans le même classeur) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
' Récupérer les informations dans les feuilles
' jobs.infos, TIR.infos, UNLOPC
  Sheets(shJob).Select
    lDebut = Cells.Find(what:="JOB_NAME").Row + 1
    lFin = ActiveSheet.UsedRange.Rows.Count
    cDebut = 1
    cFin = Cells.Find(what:="PRCT_JOB_ETAPE").Column
  Set Jobs = remplir_dictionnaire(lDebut, cDebut, lFin, cFin)

Ainsi j'accède une seule fois à ma feuille, tous mes travaux et tests se font dans mon dictionnaires qui s'appelle ici Jobs.


Dans ton cas on pourrait imaginer rajouter le fichier et la feuille à consulter, lire le contenu et refermer le tout.

Teste si le classeur est ouver, et l'ouvre en lecture seule si besoin

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
  planning = excelOuvert(fplan)
  If Not planning Then 'Si le classeur du planning n'est pas ouvert
    Workbooks.Open ch_p & "\" & fplan, ReadOnly:=True 'Il s'ouvre en lecture seule
    Workbooks(fplan).Sheets(sa).Activate 'Active une feuille spécifique
  End If

Et la fonction qui contrôle si le classeur est ouvert

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
'Contrôler si un classeur est ouvert
'@PARAM {Variant} nom du classeur à contrôler
'@RETURN {Boolean}
Function excelOuvert(Classeur As Variant) As Boolean
Dim wb As Excel.Workbook
Dim Appli As Excel.Application
On Error Resume Next
Set Appli = GetObject(, "Excel.Application")
excelOuvert = False
For Each wb In Appli.Workbooks
  If wb.Name = Classeur Then
  excelOuvert = True
Exit Function
  End If
Next wb
End Function

Une fois que tu as lu le contenu de la feuille, tu refermes le classeur

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Application.Workbooks(fplan).Close SaveChanges:=False

A adapter évidement.

[jeanmidudu]

@Bragu Demon

Je crois que ta solution n'est pas vraiment gagnante. Elle l'est par rapport à son code initial, mais quand on voit ce qu'il fait, il est assez évident (même s'il subsiste un très léger riquiqui minuscule micro doute) que tout peut se faire par copier-coller. 4 instructions maximum (et encore, je me demande si on ne peut pas faire moins) au lieu de plus de 1600...

Enfin le demandeur choisira la solution qui lui convient le mieux.

[barbeaua]

Bonjour à tous et merci pour votre implication dans mon projet.

La variable "j" est un simple compteur qui me permet de créer 4 colonnes à chaque nouvelle feuille créée.

Le problème, c'est que les modifications effectuées sur une feuille après le lancement de la procédure doivent avoir une répercution sur la feuille "bon de commande".
Je suis donc obligé de créer une sorte de lien entre celles-ci.

Je vais essayer de tout placer dans ce même classeur pour éviter le lien vers un autre fichier, en espérant que ça améliore le temps d’exécution.

Je reviendrais vers vous sur la suite.

[barbeaua]

Voilà,

Alors j'ai copié le contenu de mon fichier externe dans une nouvelle feuille appelée "prix".

Voici le code:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
Sub Commande()
 
 
With Sheets("Bon de commande")
    .Range(.Cells(1, j - 5), .Cells(1, j - 2)).Merge 'Fusion des 4 cellules de la nouvelle colonne ligne 1
    .Cells(1, j - 5) = WorksheetFunction.VLookup(resultat, Sheets("ListeIG").Range("A4:B2000"), 2, False) 'Attribution d'un nom à cette colonne (nom récupéré grâce au code entré "resultat" qui va rechercher sur une autre feuille le nom associé au code)
 
    .Cells(2, j - 5).Value = " Quantité " 'Attribution des 4 sous colonnes avec un nom ligne 2
    .Cells(2, j - 4).Value = " Fournisseur "    'Affichage des titres colonnes
    .Cells(2, j - 3).Value = " Code "
    .Cells(2, j - 2).Value = " N° Article "
 
    For i = 5 To 1677 'boucle qui parcourt la plage de données
 
    .Cells(i - 2, j - 5).FormulaLocal = "= " & Sheets(resultat).Name & "!C" & i 'lie les cellules de la nouvelle feuille (nomée avec la variable resultat) a celles de la feuille bon de commande "quantité"
    .Cells(i - 2, j - 4).FormulaLocal = "=Prix!G" & i 'lie les cellules de la colonne G de la feuille prix à celles de la colonne fournisseur dans "bon de commande"
    .Cells(i - 2, j - 3).FormulaLocal = "=Prix!H" & i 'lie les cellules de la colonne H de la feuille prix à celles de la colonne code dans "bon de commande"
    .Cells(i - 2, j - 2).FormulaLocal = "=Prix!I" & i 'lie les cellules de la colonne I de la feuille prix à celles de la colonne n° article dans "bon de commande"
 
    Next i
 
End With
 
End Sub

Le temps d'exécution est nettement plus rapide mais toujours de quelques secondes...

Voici donc le but recherché, avec un temps d'exécution plus court.
Si l'utilisateur modifie la colonne "quantité" dans une des feuilles du classeur, la modification se fera automatiquement sur la colonne associée dans "bon de commande" etc..

N'hésitez pas à me poser des questions si je n'ai pas encore été assez clair, et merci pour votre aide.

[Al__22]

La meilleurs utilisation ici serait un dictionnaire selon moi

EDIT :
Non en fait mieux tu créé un tableau avec les fonctionnalités d'excel tu auras pas besoin de à chaque fois remettre la formule suffit de la mettre dans la première cellule puis de redimensionner le tableau !!!!

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
 
'....Ton histoire d'entête (nom de colonne)
.ListObjects.Add(xlSrcRange, Range("Ton Range entête"), , xlYes).Name = "Tableau1"
 
.Cells(3, j - 5).FormulaLocal = "= " & Sheets(resultat).Name & "!C5"
.Cells(3, j - 4).FormulaLocal = "=Prix!G5" 
.Cells(3, j - 3).FormulaLocal = "=Prix!H5"
.Cells(3, j - 2).FormulaLocal = "=Prix!I5"
 
.ListObjects("Tableau1").Resize Range("Le début de ton range:la colonne de fin et 1677")