Optimisation code VBA pour les pros

Créer une discussion
Répondre
  • Initiateur de la discussion Initiateur de la discussion Amilo
  • Date de début Date de début

Boostez vos compétences Excel avec notre communauté !

Rejoignez Excel Downloads, le rendez-vous des passionnés où l'entraide fait la force. Apprenez, échangez, progressez – et tout ça gratuitement ! 👉 Inscrivez-vous maintenant !

S'inscrire
A

Amilo

XLDnaute Accro
Bonsoir,

Voici un code trés intéressant mais dont je ne suis malheureusement pas l'auteur et j'aimerais l'optimiser.

Je vous serais reconnaissant de votre aide pour résoudre si possible les 2 problèmes ci-dessous :

1- lorsque que les valeurs de la colonne A ne sont pas contigües, le code ne fonctionne plus pour les zones d'après !!! Par exemple, ici les lignes 20 à 22 et 24.

2- lorsque je supprime une valeur de la colonne D, j'ai le message d'erreur suivant : "erreur d'exécution 13, incompatibilité de type"

J'espère que le fichier est facile à comprendre sur les résultats de la feuille "devis" attendus et les liens avec la feuille "Code".

Merci d'avance pour votre soutien.

Cijoint.fr - Service gratuit de dépôt de fichiers

Cordialement
 
Trier par date Trier par votes
Re : Optimisation code VBA pour les pros

Bonsoir Amilo,

J'ai modifié ton code comme suit (lignes en bleu) et ça semble fonctionner :

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)

    ' Variable pour plage à surveiller
    Dim Plage As Range, Limite As Long
    ' Variable pour série de cellule à introduire
    Dim Serie As String, Premier As String
    ' Variable indice de la valeur recherché
    Dim ValeurRecherche As Long
    
    ' Trouver la dernière cellule vide en partant de A15 et définir C15
    [COLOR="Blue"][B]Limite = Range("A65536").End(xlUp).Row[/B][/COLOR]
    ' Définir la plage de cellules à surveiller
    [COLOR="Blue"][B]Set Plage = Range("A15:D" & Limite, "D15:C" & Limite)[/B][/COLOR]
    ' Est-on dans la plage
    If Not Intersect(Target, Plage) Is Nothing Then
.....

Espérant t'avoir aidé.

Cordialement.
 
Vote positif 0 Vote négatif
Re : Optimisation code VBA pour les pros

Papou-net à dit:
Bonsoir Amilo,

J'ai modifié ton code comme suit (lignes en bleu) et ça semble fonctionner :

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)

    ' Variable pour plage à surveiller
    Dim Plage As Range, Limite As Long
    ' Variable pour série de cellule à introduire
    Dim Serie As String, Premier As String
    ' Variable indice de la valeur recherché
    Dim ValeurRecherche As Long
    
    ' Trouver la dernière cellule vide en partant de A15 et définir C15
    [COLOR="Blue"][B]Limite = Range("A65536").End(xlUp).Row[/B][/COLOR]
    ' Définir la plage de cellules à surveiller
    [COLOR="Blue"][B]Set Plage = Range("A15:D" & Limite, "D15:C" & Limite)[/B][/COLOR]
    ' Est-on dans la plage
    If Not Intersect(Target, Plage) Is Nothing Then
.....

Espérant t'avoir aidé.

Cordialement.
Cliquez pour agrandir...

Bonsoir Papou-net,

Bravo pour votre rapidité et cette solution.

Vous m'avez brillamment aidé et je vous en remercie.

Par contre sauf erreur de ma part, la modification de votre 1ère ligne en bleu suffit pour résoudre le problème car la 2ème ligne est inchangée !!

De plus, mon 2ème problème en point 2 subsiste avec le message d'erreur lors de la suppression d'une valeur en colonne D, mais c'est pas dramatique je peux vivre avec !!

Sinon, y'a-t'il une touche pour réactualiser le code sans devoir quitter le fichier et le rouvrir à nouveau lorsque le message d'erreur est généré ???

Merci d'avance

Slts
 
Vote positif 0 Vote négatif
Re : Optimisation code VBA pour les pros

Re bonsoir Amilo,

Pour le point 1 tu as raison, mon cerveau a flanché.

Pour le point 2, essaies en modifiant ton code ainsi :

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)

[COLOR="Blue"][B]On Error Resume Next[/B][/COLOR]

    ' Variable pour plage à surveiller
    Dim Plage As Range, Limite As Long
    ' Variable pour série de cellule à introduire
    Dim Serie As String, Premier As String
    ' Variable indice de la valeur recherché
    Dim ValeurRecherche As Long
    
    ' Trouver la dernière cellule vide en partant de A15 et définir C15
    Limite = Range("A15:A65536").End(xlDown).Row
    ' Définir la plage de cellules à surveiller
    Set Plage = Range("A15:D" & Limite, "D15:C" & Limite)
    ' Est-on dans la plage
    If Not Intersect(Target, Plage) Is Nothing Then
.....

Cordialement.
 
Vote positif 0 Vote négatif
Re : Optimisation code VBA pour les pros

Papou-net à dit:
Re bonsoir Amilo,

Pour le point 1 tu as raison, mon cerveau a flanché.

Pour le point 2, essaies en modifiant ton code ainsi :

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)

[COLOR="Blue"][B]On Error Resume Next[/B][/COLOR]

    ' Variable pour plage à surveiller
    Dim Plage As Range, Limite As Long
    ' Variable pour série de cellule à introduire
    Dim Serie As String, Premier As String
    ' Variable indice de la valeur recherché
    Dim ValeurRecherche As Long
    
    ' Trouver la dernière cellule vide en partant de A15 et définir C15
    Limite = Range("A15:A65536").End(xlDown).Row
    ' Définir la plage de cellules à surveiller
    Set Plage = Range("A15:D" & Limite, "D15:C" & Limite)
    ' Est-on dans la plage
    If Not Intersect(Target, Plage) Is Nothing Then
.....

Cordialement.
Cliquez pour agrandir...

Mille mercis Papou-net,

Vous êtes trop fort, ça marche super, je suis trés content !!!

Je reviendrais peut-être un autre jour sur un autre fil pour du soutien afin de remplacer la formule Excel de la colonne L par du VBA mais en attendant c'est moi qui m'y colle en espérant réussir.

Je pense que ça sera plus pratique avec du code VBA !!

Bonne soirée à tous
 
Vote positif 0 Vote négatif
Re : Optimisation code VBA pour les pros

Bonjour le Forum, Bonjour Papou-net,

Désolé, je reviens avec une nouvelle demande car je viens de constater un petit désagrément à l'instant.

Lorsque dans la feuille "Code", j'indique des prix unitaires avec des centimes par exemple 10,50 au lieu de 10,00, dans ma feuille "Devis" la cellule m'indique un format stocké sous "texte" !!!

Je suis obligé de convertir manuellement en format nombre à chaque fois.

J'ai tout essayé au niveau Excel mais sans succés, je pense que c'est au niveau de la macro qu'il faut intervenir !!

Ci-joint le fichier :

Cijoint.fr - Service gratuit de dépôt de fichiers
Merci d'avance pour votre aide.

Slts
 
Vote positif 0 Vote négatif
Re : Optimisation code VBA pour les pros

Staple1600 à dit:
bonjour


si tu compresses ton fichier, il fait seulement 28 ko

tu peux donc le poster sur le forum
Cliquez pour agrandir...


Bonjour Staple1600,

J'ai cet ordinateur depuis moins d'1 semaine et je n'ai pas installé de logiciel de compression à ce jour.
Je pense que j'aurais besoin d'installer Winrar notamment pour cela.

Sinon, vous n'arrivez pas à ouvrir le fichier sur le site "ci-joint" ??

Slts
 
Vote positif 0 Vote négatif
Re : Optimisation code VBA pour les pros

Bonjour Amilo,

Le problème vient du format décimal des montants, car avec les autres cellules le format reste correct.
Je n'en ai pas encore trouvé la cause, mais je vais y regarder dès que possible (j'ai un week-end chargé).

A bientôt.

Cordialement.
 
Dernière édition:
Vote positif 0 Vote négatif
Re : Optimisation code VBA pour les pros

Re bonjour Amilo, JM

Ca y est, j'ai mis la main sur le problème : il suffit de convertir la variable issue de la recherche en variable décimale (voir les lignes bleues).

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
    ' Variable pour plage à surveiller
    Dim Plage As Range, Limite As Long
    ' Variable pour série de cellule à introduire
    Dim Serie As String, Premier As String
    ' Variable indice de la valeur recherché
    Dim ValeurRecherche As Long
    
    ' Trouver la dernière cellule vide en partant de A15 et définir C15
    Limite = Range("A65536").End(xlDown).Row
    ' Définir la plage de cellules à surveiller
    Set Plage = Range("A15:D" & Limite, "D15:C" & Limite)
    ' Est-on dans la plage
    If Not Intersect(Target, Plage) Is Nothing Then
        ' Si oui, désactive les évènements pour éviter la récursivité de l'évènement
        Application.EnableEvents = False
        ' En fonction de la colonne sélectionner, on travail sur la cellule [Target]
        Select Case Target.Column
            ' Colonne A de la feuille devis
            Case 1:
                                                   
                ' Code -> Colonne D Description de la deuille devis
                ' La fonction F_CODE modifie la valeur de [Premier]
                Serie = F_CODE(Target.Value, Premier)
                Target.Offset(0, 3).Value = ""
                Target.Offset(0, 3).Value = Premier
                Target.Offset(0, 3).Validation.Delete
                ' Affectation de la série à la cellule
                Target.Offset(0, 3).Validation.Add Type:=xlValidateList, _
                                                   AlertStyle:=xlValidAlertStop, _
                                                   Operator:=xlBetween, _
                                                   Formula1:=Serie
                                                   
                ' Code -> Colonne C
                Target.Offset(0, 2).Value = ""
                Target.Offset(0, 2).Value = Recherche(Target.Offset(0, 3).Value, Target.Value, cteCode)
                
                ' Unité de mesure -> Colonne I
                Target.Offset(0, 8).Value = ""
                Target.Offset(0, 8).Value = Recherche(Target.Offset(0, 3).Value, Target.Value, cteUnit)
                
                ' Prix Unitaire -> Colonne K
                Target.Offset(0, 10).Value = ""
                [COLOR="Blue"][B]Target.Offset(0, 10).Value = CDbl(Recherche(Target.Offset(0, 3).Value, Target.Value, ctePrix))[/B][/COLOR]
            ' Colonne D
            Case 4:
                ' Description
                Target.Offset(0, -1).Value = ""
                Target.Offset(0, -1).Value = Recherche(Target.Value, Target.Offset(0, -3).Value, cteCode)
                
                ' Unité de mesure -> Colonne I
                Target.Offset(0, 1).Value = ""
                Target.Offset(0, 1).Value = Recherche(Target.Value, Target.Offset(0, -3).Value, cteUnit)
                
                ' Prix Unitaire -> Colonne K
                Target.Offset(0, 3).Value = ""
                [COLOR="Blue"][B]Target.Offset(0, 3).Value = CDbl(Recherche(Target.Value, Target.Offset(0, -3).Value, ctePrix))[/B][/COLOR]
            ' Colonne Inconnu
            Case Else:
                'Ne fait rien sauf répondre aux évènements s'il y a.
                DoEvents
        End Select
        Application.EnableEvents = True
    End If
     
End Sub

Bon WE.

Cordialement.
 
Dernière édition:
Vote positif 0 Vote négatif
Re : Optimisation code VBA pour les pros

Papou-net à dit:
Re bonjour Amilo, JM

Ca y est, j'ai mis la main sur le problème : il suffit de convertir la variable issue de la recherche en variable décimale (voir les lignes bleues).

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
    ' Variable pour plage à surveiller
    Dim Plage As Range, Limite As Long
    ' Variable pour série de cellule à introduire
    Dim Serie As String, Premier As String
    ' Variable indice de la valeur recherché
    Dim ValeurRecherche As Long
    
    ' Trouver la dernière cellule vide en partant de A15 et définir C15
    Limite = Range("A65536").End(xlDown).Row
    ' Définir la plage de cellules à surveiller
    Set Plage = Range("A15:D" & Limite, "D15:C" & Limite)
    ' Est-on dans la plage
    If Not Intersect(Target, Plage) Is Nothing Then
        ' Si oui, désactive les évènements pour éviter la récursivité de l'évènement
        Application.EnableEvents = False
        ' En fonction de la colonne sélectionner, on travail sur la cellule [Target]
        Select Case Target.Column
            ' Colonne A de la feuille devis
            Case 1:
                                                   
                ' Code -> Colonne D Description de la deuille devis
                ' La fonction F_CODE modifie la valeur de [Premier]
                Serie = F_CODE(Target.Value, Premier)
                Target.Offset(0, 3).Value = ""
                Target.Offset(0, 3).Value = Premier
                Target.Offset(0, 3).Validation.Delete
                ' Affectation de la série à la cellule
                Target.Offset(0, 3).Validation.Add Type:=xlValidateList, _
                                                   AlertStyle:=xlValidAlertStop, _
                                                   Operator:=xlBetween, _
                                                   Formula1:=Serie
                                                   
                ' Code -> Colonne C
                Target.Offset(0, 2).Value = ""
                Target.Offset(0, 2).Value = Recherche(Target.Offset(0, 3).Value, Target.Value, cteCode)
                
                ' Unité de mesure -> Colonne I
                Target.Offset(0, 8).Value = ""
                Target.Offset(0, 8).Value = Recherche(Target.Offset(0, 3).Value, Target.Value, cteUnit)
                
                ' Prix Unitaire -> Colonne K
                Target.Offset(0, 10).Value = ""
                [COLOR="Blue"][B]Target.Offset(0, 10).Value = CDbl(Recherche(Target.Offset(0, 3).Value, Target.Value, ctePrix))[/B][/COLOR]
            ' Colonne D
            Case 4:
                ' Description
                Target.Offset(0, -1).Value = ""
                Target.Offset(0, -1).Value = Recherche(Target.Value, Target.Offset(0, -3).Value, cteCode)
                
                ' Unité de mesure -> Colonne I
                Target.Offset(0, 1).Value = ""
                Target.Offset(0, 1).Value = Recherche(Target.Value, Target.Offset(0, -3).Value, cteUnit)
                
                ' Prix Unitaire -> Colonne K
                Target.Offset(0, 3).Value = ""
                [COLOR="Blue"][B]Target.Offset(0, 3).Value = CDbl(Recherche(Target.Value, Target.Offset(0, -3).Value, ctePrix))[/B][/COLOR]
            ' Colonne Inconnu
            Case Else:
                'Ne fait rien sauf répondre aux évènements s'il y a.
                DoEvents
        End Select
        Application.EnableEvents = True
    End If
     
End Sub

Bon WE.

Cordialement.
Cliquez pour agrandir...

Re-bonjour le Forum, Staple1600, Papou-net,

Désolé pour le retard, car j'ai dû m'absenter toute l'après-midi.

Un grand merci Papou-net pour cette solution qui fonctionne sans problème, décidément vous êtes un pro en VBA !!
Si vous avez de bons conseils pour apprendre VBA, je suis preneur.
J'ai pour le moment plusieurs livres récents en VBA et un CD de formation à VBA, je rame énormément pour le moment...

Staple1600, que je suis nul, vous avez raison, j'ai jamais utilisé cette commande de Windows, les rares fois que j'ai compréssé un fichier c'était avec Winrar.

Dans le futur, je penserai à compresser les fichiers pour alléger mes messages.

Merci encore.

Bon Week-end à tous.
 
Vote positif 0 Vote négatif
Re : Optimisation code VBA pour les pros

Bonjour Amilo,

Ma modestie dût-elle en souffrir, les éloges contenues dans tes derniers messages me vont droit au cœur, même si elles me paraissent disproportionnées (ce n’est pas un reproche mais la simple expression de ma conviction personnelle). En effet, je pense que le « Savoir sans partage est comme l’oiseau sans ailes : il n’élève pas son détenteur » !
A mon tour de passer aux compliments : ton respect vis-à-vis de la charte du forum et de ses participants sont un encouragement à la poursuite des activités de ce site. Ce n’est malheureusement que trop rarement perceptible de nos jours, où l’individualisme prend souvent trop de place dans notre société.
Pour répondre à ton désir de progresser dans l’utilisation d’Excel et de VBA, je viens t’apporter mes modestes conseils :
Tu possèdes de la littérature récente, c’est déjà une base importante, mais ce n’est pas l’essentiel. Le plus important, à mon sens, est le désir d’aller plus loin, ce que tu sembles exprimer en participant à ce genre de forum. Il ne faut pas hésiter à aiguiser ta curiosité en lisant un maximum de rubriques sur ce site (ou d’autres du même genre), et qui sont une source quasi-inépuisable de solutions ou astuces aussi variées qu’ efficaces. Il est important également de chercher à créer des programmes qui n’ont pas forcément un caractère indispensable mais qui t’aideront dans la maîtrise de VBA (ça peut être des jeux simples style Sudoku, applications style CD/DVDthèque ou gestion de compte bancaire, etc…). Pas besoin de se répandre tous azimuts, il vaut mieux se limiter à une ou deux idées mais les mener à leur terme. Ne pas oublier non plus les rubriques d’aide d’Excel et les enregistrements de macros qui t’en apprendront beaucoup tout en te dépannant immédiatement. Une dernière piste est de faire appel à l’ami Google qui te conduira à une multitude de sites dédiés à ce sujet.
Voilà, je pense, un tour d’horizon des possibilités mises à ta disposition et qui t’aideront à entrer un jour dans le club des « Barbatrucs », en tout cas c’est ce que je te souhaite.

Très cordialement.
 
Vote positif 0 Vote négatif
Re : Optimisation code VBA pour les pros

Bonjour Amilo, Salut à tous,

Un conseil. Il est inutile (sauf quand c'est vraiment utile) de citer systematiquement les messages précédents. Cela rallonge le post et devient un moment donné très long et ne facilite pas la lisibilité.
 
Vote positif 0 Vote négatif
Re : Optimisation code VBA pour les pros

Bonjour le Forum, Raja, Papou-net,

Merci Papou-net pour vos conseils,

Voici les 2 points cités qui me manquent et qui me demandent à être exploités :

1- "Pas besoin de se répandre tous azimuts, il vaut mieux se limiter à une ou deux idées mais les mener à leur terme."

Il est vrai que j'ai trop d'idées (projets de formations) qui se bousculent et trés peu se concrétisent ou sont réalisées de manière brouillone...

2- un autre point intéréssant à mettre en pratique :

"Il est important également de chercher à créer des programmes qui n’ont pas forcément un caractère indispensable mais qui t’aideront dans la maîtrise de VBA (ça peut être des jeux simples style Sudoku, applications style CD/DVDthèque ou gestion de compte bancaire, etc…)."


Sinon, pour ce qui est des compliments, voici 2 explications :

1- vous n'êtes pas le concepteur du code VBA, et à ma connaissance ceci le rend plus difficile à le comprendre et à résoudre tout problème ou optimisation pour quelqu'un d'autres. (même s'il y a déjà de bonnes explications entre les lignes du code);

Aussi, pour le débutant que je suis, je m'attendais à un délai de réponse beaucoup plus long pour mon 1er problème (au moins pour le lendemain...) alors que vous avez été trés rapide dans la compréhension du code et dans sa réponse et donc trés surpris !!

2- Et également trés content que tous les points d'amélioration sont résolus (enfin je l'espère..)

Raja, vous faites bien de souligner mes messages citant à chaque fois les précédentes réponses et ceci il est vrai inutilement.

Je ne me suis pas encore familiarisé avec le bouton "Ajouter une réponse" en bas à gauche du fil mais c'est chose faite...

Merci encore

Bon dimanche à tous.
 
Dernière édition:
Vote positif 0 Vote négatif
Rejoins nos membres "Supporter XLD" et booste ton expérience Excel !
- Navigue sans publicité
- Accède à Cléa, notre assistante IA experte Excel... et pas que...
- Profite de fonctionnalités exclusives
Ton soutien permet à Excel Downloads de rester 100% gratuit et de continuer à rassembler les passionnés d'Excel.
Je deviens Supporter XLD

Discussions similaires

A
  • Résolu(e)
XL 2019 Optimisation code VBA (Icônes flottantes)
Réponses
14
Affichages
713
Amilo
A
D
  • Résolu(e)
Microsoft 365 Assistance pour optimisation d'un code VBA qui ne fonctionne pas comme souhaité
2
Réponses
21
Affichages
1 K
desto
D
ExcelDow
  • Résolu(e)
Microsoft 365 Modifier un code copie donnée chaque feuil séparément
Réponses
11
Affichages
424
ExcelDow
ExcelDow
pascal21
  • Question Question
XL 2016 intégrer une macro sur les nouvelles feuilles crées
Réponses
6
Affichages
228
pascal21
pascal21
S
  • Question Question
Microsoft 365 code vba pour chaine de caractère
Réponses
4
Affichages
521
vgendron
vgendron
E
  • Résolu(e)
Microsoft 365 Reduire Temps Execution Code
Réponses
5
Affichages
486
eric72
E
A
  • Question Question
Microsoft 365 Tri sur feuille protégée +Aide protection de données déjà saisies
Réponses
2
Affichages
520
TooFatBoy
TooFatBoy
anthoYS
  • Résolu(e)
XL 2019 colorer les différents aliments avec un code VB pour macro de ce genre ?
2
Réponses
16
Affichages
616
anthoYS
anthoYS
D
  • Question Question
Microsoft 365 Code VBA pour date DD/MM/YYYY
Réponses
6
Affichages
636
Staple1600
Staple1600
Lu76Fer
  • Résolu(e)
XL 2016 XLS - Comment déterminer les zones présentent au niveau d'une cellule ?
Réponses
2
Affichages
846
Lu76Fer
Lu76Fer
G
  • Question Question
XL 2021 Macro pour changer les titres de fichier d'archive
Réponses
3
Affichages
699
fanch55
fanch55
N
  • Résolu(e)
Microsoft 365 Aide pour un code Vba
2 3
Réponses
32
Affichages
2 K
Nicolas JACQUIN
Nicolas JACQUIN
T
  • Résolu(e)
XL 2019 Filtrer certains champs au démarrage avec vba
2
Réponses
15
Affichages
1 K
telemarrk
T
J
  • Question Question
Microsoft 365 EXCEL code VBA pour réinitialiser les listes déroulantes en cascades
Réponses
1
Affichages
484
fanch55
fanch55
A
  • Résolu(e)
Microsoft 365 Correction du code vba
Réponses
5
Affichages
662
AD95
A
C
  • Résolu(e)
Microsoft 365 Problème VBA lors de l'affichage de ligne masquée
2
Réponses
21
Affichages
1 K
Chris Linefield
C
M
  • Question Question
Microsoft 365 Question code VBA
Réponses
2
Affichages
613
Maxou69
M
M
  • Question Question
XL 2019 Optimisation de code VBA
2 3
Réponses
33
Affichages
3 K
mmaiga
M
A
  • Résolu(e)
Microsoft 365 Correction d'un code vba
2
Réponses
22
Affichages
2 K
AD95
A
C
  • Résolu(e)
XL 2016 Répartir dans des Groupes d'Effectifs Aléatoires ... mais pas trop
Réponses
2
Affichages
596
crocrocro
C
Retour