Logo GNT
Actualités Tests & Guides Bons Plans
Twitter iPhone pliant OnePlus 11 PS5 Disney+ Orange Livebox Windows 11
Entraide Applications Microsoft Office Excel Plantage dans le code

Plantage dans le code

11 réponses
Avatar
Sunburn
Bonjour,j'ai ce code VBA qui plante,mais je n'arrive pas à identifié le
problème ...
il n'y a pas d'autres macro sur mon classeur.
en fait, j'ai des listes de choix en G40 et G41, et ça doit impliquer des
réponses en G42 et G43

merci de votre aide.
YANN
----
Private Sub Worksheet_Change(ByVal Target As Range)
Set inter = Intersect(Target, Range("G40:G41"))
If Not inter Is Nothing Then
DeclarationsAFaire
End If
End Sub

Sub DeclarationsAFaire()
Application.ScreenUpdating = False
If [g40] = "IS" And [g41] = "Normal" Then: [G42] = "2065": [G43] = "2050"
If [g40] = "IS" And [g41] = "Simplifié" Then: [G42] = "2065": [G43] = "2033"
If [g40] = "IS" And [g41] = "N/A" Then: [G42] = "ERREUR": [G43] = "ERREUR"
If [g40] = "IR" And [g41] = "Normal" Then: [G42] = "2031": [G43] = "2050"
If [g40] = "IR" And [g41] = "Simplifié" Then: [G42] = "2031": [G43] = "2033"
If [g40] = "IR" And [g41] = "N/A" Then: [G42] = "ERREUR": [G43] = "ERREUR"
If [g40] = "BA IR" And [g41] = "Normal" Then: [G42] = "2143": [G43] = "2144"
If [g40] = "BA IR" And [g41] = "Simplifié" Then: [G42] = "2139": [G43] = ""
If [g40] = "BA IR" And [g41] = "N/A" Then: [G42] = "ERREUR": [G43] = "ERREUR"
If [g40] = "BNC spécial" And [g41] = "Normal" Then: [G42] = "ERREUR": [G43]
= "ERREUR"
If [g40] = "BNC spécial" And [g41] = "Simplifié" Then: [G42] = "ERREUR":
[G43] = "ERREUR"
If [g40] = "BNC spécial" And [g41] = "N/A" Then: [G42] = "Sur la 2042":
[G43] = ""
If [g40] = "BNC déclaration contrôlée" And [g41] = "Normal" Then: [G42] =
"ERREUR": [G43] = "ERREUR"
If [g40] = "BNC déclaration contrôlée" And [g41] = "Simplifié" Then: [G42] =
"ERREUR": [G43] = "ERREUR"
If [g40] = "BNC déclaration contrôlée" And [g41] = "N/A" Then: [G42] =
"2035": [G43] = ""
If [g40] = "Non fiscalisé" And [g41] = "Normal" Then: [G42] = "ERREUR":
[G43] = "ERREUR"
If [g40] = "Non fiscalisé" And [g41] = "Simplifié" Then: [G42] = "ERREUR":
[G43] = "ERREUR"
If [g40] = "Non fiscalisé" And [g41] = "N/A" Then: [G42] = "N/A": [G43] =
"N/A"
If [g40] = "" And [g41] = "" Then: [G42] = "": [G43] = ""
End Sub
-------
Signaler un problème avec ce contenu

10 réponses

Supprimer
Cette action est irreversible, confirmez la suppression du commentaire ?
Signaler le commentaire

Veuillez sélectionner un problème

Nudité
Violence
Harcèlement
Fraude
Vente illégale
Discours haineux
Terrorisme
Autre
1 2
Avatar
michdenis
En dessous de cette ligne
Private Sub Worksheet_Change(ByVal Target As Range)

tu ajoutes :
Stop

et tu exécutes toute ta macro en pas à pas.
ça devrait te permettre la ligne de code problématique
peut être pourrons-nous t'aider !



"Sunburn" a écrit dans le message de groupe de
discussion :
Bonjour,j'ai ce code VBA qui plante,mais je n'arrive pas à identifié le
problème ...
il n'y a pas d'autres macro sur mon classeur.
en fait, j'ai des listes de choix en G40 et G41, et ça doit impliquer des
réponses en G42 et G43

merci de votre aide.
YANN
----
Private Sub Worksheet_Change(ByVal Target As Range)
Set inter = Intersect(Target, Range("G40:G41"))
If Not inter Is Nothing Then
DeclarationsAFaire
End If
End Sub

Sub DeclarationsAFaire()
Application.ScreenUpdating = False
If [g40] = "IS" And [g41] = "Normal" Then: [G42] = "2065": [G43] = "2050"
If [g40] = "IS" And [g41] = "Simplifié" Then: [G42] = "2065": [G43] = "2033"
If [g40] = "IS" And [g41] = "N/A" Then: [G42] = "ERREUR": [G43] = "ERREUR"
If [g40] = "IR" And [g41] = "Normal" Then: [G42] = "2031": [G43] = "2050"
If [g40] = "IR" And [g41] = "Simplifié" Then: [G42] = "2031": [G43] = "2033"
If [g40] = "IR" And [g41] = "N/A" Then: [G42] = "ERREUR": [G43] = "ERREUR"
If [g40] = "BA IR" And [g41] = "Normal" Then: [G42] = "2143": [G43] = "2144"
If [g40] = "BA IR" And [g41] = "Simplifié" Then: [G42] = "2139": [G43] = ""
If [g40] = "BA IR" And [g41] = "N/A" Then: [G42] = "ERREUR": [G43] = "ERREUR"
If [g40] = "BNC spécial" And [g41] = "Normal" Then: [G42] = "ERREUR": [G43]
= "ERREUR"
If [g40] = "BNC spécial" And [g41] = "Simplifié" Then: [G42] = "ERREUR":
[G43] = "ERREUR"
If [g40] = "BNC spécial" And [g41] = "N/A" Then: [G42] = "Sur la 2042":
[G43] = ""
If [g40] = "BNC déclaration contrôlée" And [g41] = "Normal" Then: [G42] "ERREUR": [G43] = "ERREUR"
If [g40] = "BNC déclaration contrôlée" And [g41] = "Simplifié" Then: [G42] "ERREUR": [G43] = "ERREUR"
If [g40] = "BNC déclaration contrôlée" And [g41] = "N/A" Then: [G42] "2035": [G43] = ""
If [g40] = "Non fiscalisé" And [g41] = "Normal" Then: [G42] = "ERREUR":
[G43] = "ERREUR"
If [g40] = "Non fiscalisé" And [g41] = "Simplifié" Then: [G42] = "ERREUR":
[G43] = "ERREUR"
If [g40] = "Non fiscalisé" And [g41] = "N/A" Then: [G42] = "N/A": [G43] "N/A"
If [g40] = "" And [g41] = "" Then: [G42] = "": [G43] = ""
End Sub
-------
Avatar
garnote
Salut,

J'ai essayé tes macros et tout est OK !
Chez toi, ça plante ä quelle ligne ?
Attention aux sauts de lignes.

Private Sub Worksheet_Change(ByVal Target As Range)
Set inter = Intersect(Target, Range("G40:G41"))
If Not inter Is Nothing Then
DeclarationsAFaire
End If
End Sub

Sub DeclarationsAFaire()
Application.ScreenUpdating = False
If [g40] = "IS" And [g41] = "Normal" Then: [G42] = "2065": [G43] = "2050"
If [g40] = "IS" And [g41] = "Simplifié" Then: [G42] = "2065": [G43] = "2033"
If [g40] = "IS" And [g41] = "N/A" Then: [G42] = "ERREUR": [G43] = "ERREUR"
If [g40] = "IR" And [g41] = "Normal" Then: [G42] = "2031": [G43] = "2050"
If [g40] = "IR" And [g41] = "Simplifié" Then: [G42] = "2031": [G43] = "2033"
If [g40] = "IR" And [g41] = "N/A" Then: [G42] = "ERREUR": [G43] = "ERREUR"
If [g40] = "BA IR" And [g41] = "Normal" Then: [G42] = "2143": [G43] = "2144"
If [g40] = "BA IR" And [g41] = "Simplifié" Then: [G42] = "2139": [G43] = ""
If [g40] = "BA IR" And [g41] = "N/A" Then: [G42] = "ERREUR": [G43] = "ERREUR"
If [g40] = "BNC spécial" And [g41] = "Normal" Then: [G42] = "ERREUR": [G43] = "ERREUR"
If [g40] = "BNC spécial" And [g41] = "Simplifié" Then: [G42] = "ERREUR": [G43] = "ERREUR"
If [g40] = "BNC spécial" And [g41] = "N/A" Then: [G42] = "Sur la 2042": [G43] = ""
If [g40] = "BNC déclaration contrôlée" And [g41] = "Normal" Then: [G42] = "ERREUR": [G43] = "ERREUR"
If [g40] = "BNC déclaration contrôlée" And [g41] = "Simplifié" Then: [G42] = "ERREUR": [G43] = "ERREUR"
If [g40] = "BNC déclaration contrôlée" And [g41] = "N/A" Then: [G42] = "2035": [G43] = ""
If [g40] = "Non fiscalisé" And [g41] = "Normal" Then: [G42] = "ERREUR": [G43] = "ERREUR"
If [g40] = "Non fiscalisé" And [g41] = "Simplifié" Then: [G42] = "ERREUR": [G43] = "ERREUR"
If [g40] = "Non fiscalisé" And [g41] = "N/A" Then: [G42] = "N/A": [G43] = "N/A"
If [g40] = "" And [g41] = "" Then: [G42] = "": [G43] = ""
Application.ScreenUpdating = True
End Sub

Serge































"Sunburn" a écrit dans le message de news:

Bonjour,j'ai ce code VBA qui plante,mais je n'arrive pas à identifié le
problème ...
il n'y a pas d'autres macro sur mon classeur.
en fait, j'ai des listes de choix en G40 et G41, et ça doit impliquer des
réponses en G42 et G43

merci de votre aide.
YANN
----
Private Sub Worksheet_Change(ByVal Target As Range)
Set inter = Intersect(Target, Range("G40:G41"))
If Not inter Is Nothing Then
DeclarationsAFaire
End If
End Sub

Sub DeclarationsAFaire()
Application.ScreenUpdating = False
If [g40] = "IS" And [g41] = "Normal" Then: [G42] = "2065": [G43] = "2050"
If [g40] = "IS" And [g41] = "Simplifié" Then: [G42] = "2065": [G43] = "2033"
If [g40] = "IS" And [g41] = "N/A" Then: [G42] = "ERREUR": [G43] = "ERREUR"
If [g40] = "IR" And [g41] = "Normal" Then: [G42] = "2031": [G43] = "2050"
If [g40] = "IR" And [g41] = "Simplifié" Then: [G42] = "2031": [G43] = "2033"
If [g40] = "IR" And [g41] = "N/A" Then: [G42] = "ERREUR": [G43] = "ERREUR"
If [g40] = "BA IR" And [g41] = "Normal" Then: [G42] = "2143": [G43] = "2144"
If [g40] = "BA IR" And [g41] = "Simplifié" Then: [G42] = "2139": [G43] = ""
If [g40] = "BA IR" And [g41] = "N/A" Then: [G42] = "ERREUR": [G43] = "ERREUR"
If [g40] = "BNC spécial" And [g41] = "Normal" Then: [G42] = "ERREUR": [G43]
= "ERREUR"
If [g40] = "BNC spécial" And [g41] = "Simplifié" Then: [G42] = "ERREUR":
[G43] = "ERREUR"
If [g40] = "BNC spécial" And [g41] = "N/A" Then: [G42] = "Sur la 2042":
[G43] = ""
If [g40] = "BNC déclaration contrôlée" And [g41] = "Normal" Then: [G42] > "ERREUR": [G43] = "ERREUR"
If [g40] = "BNC déclaration contrôlée" And [g41] = "Simplifié" Then: [G42] > "ERREUR": [G43] = "ERREUR"
If [g40] = "BNC déclaration contrôlée" And [g41] = "N/A" Then: [G42] > "2035": [G43] = ""
If [g40] = "Non fiscalisé" And [g41] = "Normal" Then: [G42] = "ERREUR":
[G43] = "ERREUR"
If [g40] = "Non fiscalisé" And [g41] = "Simplifié" Then: [G42] = "ERREUR":
[G43] = "ERREUR"
If [g40] = "Non fiscalisé" And [g41] = "N/A" Then: [G42] = "N/A": [G43] > "N/A"
If [g40] = "" And [g41] = "" Then: [G42] = "": [G43] = ""
End Sub
-------


Avatar
garnote
Bonjour,

Regarde à cette adresse :

http://www.cijoint.fr/cjlink.php?file=cj200902/cijkNlkl5H.xls


Cela te convient-il ?

Serge









"Sunburn" a écrit dans le message de news:

Bonjour,j'ai ce code VBA qui plante,mais je n'arrive pas à identifié le
problème ...
il n'y a pas d'autres macro sur mon classeur.
en fait, j'ai des listes de choix en G40 et G41, et ça doit impliquer des
réponses en G42 et G43

merci de votre aide.
YANN
----
Private Sub Worksheet_Change(ByVal Target As Range)
Set inter = Intersect(Target, Range("G40:G41"))
If Not inter Is Nothing Then
DeclarationsAFaire
End If
End Sub

Sub DeclarationsAFaire()
Application.ScreenUpdating = False
If [g40] = "IS" And [g41] = "Normal" Then: [G42] = "2065": [G43] = "2050"
If [g40] = "IS" And [g41] = "Simplifié" Then: [G42] = "2065": [G43] = "2033"
If [g40] = "IS" And [g41] = "N/A" Then: [G42] = "ERREUR": [G43] = "ERREUR"
If [g40] = "IR" And [g41] = "Normal" Then: [G42] = "2031": [G43] = "2050"
If [g40] = "IR" And [g41] = "Simplifié" Then: [G42] = "2031": [G43] = "2033"
If [g40] = "IR" And [g41] = "N/A" Then: [G42] = "ERREUR": [G43] = "ERREUR"
If [g40] = "BA IR" And [g41] = "Normal" Then: [G42] = "2143": [G43] = "2144"
If [g40] = "BA IR" And [g41] = "Simplifié" Then: [G42] = "2139": [G43] = ""
If [g40] = "BA IR" And [g41] = "N/A" Then: [G42] = "ERREUR": [G43] = "ERREUR"
If [g40] = "BNC spécial" And [g41] = "Normal" Then: [G42] = "ERREUR": [G43]
= "ERREUR"
If [g40] = "BNC spécial" And [g41] = "Simplifié" Then: [G42] = "ERREUR":
[G43] = "ERREUR"
If [g40] = "BNC spécial" And [g41] = "N/A" Then: [G42] = "Sur la 2042":
[G43] = ""
If [g40] = "BNC déclaration contrôlée" And [g41] = "Normal" Then: [G42] > "ERREUR": [G43] = "ERREUR"
If [g40] = "BNC déclaration contrôlée" And [g41] = "Simplifié" Then: [G42] > "ERREUR": [G43] = "ERREUR"
If [g40] = "BNC déclaration contrôlée" And [g41] = "N/A" Then: [G42] > "2035": [G43] = ""
If [g40] = "Non fiscalisé" And [g41] = "Normal" Then: [G42] = "ERREUR":
[G43] = "ERREUR"
If [g40] = "Non fiscalisé" And [g41] = "Simplifié" Then: [G42] = "ERREUR":
[G43] = "ERREUR"
If [g40] = "Non fiscalisé" And [g41] = "N/A" Then: [G42] = "N/A": [G43] > "N/A"
If [g40] = "" And [g41] = "" Then: [G42] = "": [G43] = ""
End Sub
-------


Avatar
Sunburn
oui, désolé,
ça plante à la ligne qui est concerné par le changement que j'applique en
G40 ou G41.....
par exemple, si je choisi "IS" en G40, il plante à la première ligne de ma
macro :
----- : [G42] = "2065" ------ et ça c'est surligné en jaune.
je comprend pas.
merci de ton aide.
YANN

"garnote" a écrit :

Salut,

J'ai essayé tes macros et tout est OK !
Chez toi, ça plante ä quelle ligne ?
Attention aux sauts de lignes.

Private Sub Worksheet_Change(ByVal Target As Range)
Set inter = Intersect(Target, Range("G40:G41"))
If Not inter Is Nothing Then
DeclarationsAFaire
End If
End Sub

Sub DeclarationsAFaire()
Application.ScreenUpdating = False
If [g40] = "IS" And [g41] = "Normal" Then: [G42] = "2065": [G43] = "2050"
If [g40] = "IS" And [g41] = "Simplifié" Then: [G42] = "2065": [G43] = "2033"
If [g40] = "IS" And [g41] = "N/A" Then: [G42] = "ERREUR": [G43] = "ERREUR"
If [g40] = "IR" And [g41] = "Normal" Then: [G42] = "2031": [G43] = "2050"
If [g40] = "IR" And [g41] = "Simplifié" Then: [G42] = "2031": [G43] = "2033"
If [g40] = "IR" And [g41] = "N/A" Then: [G42] = "ERREUR": [G43] = "ERREUR"
If [g40] = "BA IR" And [g41] = "Normal" Then: [G42] = "2143": [G43] = "2144"
If [g40] = "BA IR" And [g41] = "Simplifié" Then: [G42] = "2139": [G43] = ""
If [g40] = "BA IR" And [g41] = "N/A" Then: [G42] = "ERREUR": [G43] = "ERREUR"
If [g40] = "BNC spécial" And [g41] = "Normal" Then: [G42] = "ERREUR": [G43] = "ERREUR"
If [g40] = "BNC spécial" And [g41] = "Simplifié" Then: [G42] = "ERREUR": [G43] = "ERREUR"
If [g40] = "BNC spécial" And [g41] = "N/A" Then: [G42] = "Sur la 2042": [G43] = ""
If [g40] = "BNC déclaration contrôlée" And [g41] = "Normal" Then: [G42] = "ERREUR": [G43] = "ERREUR"
If [g40] = "BNC déclaration contrôlée" And [g41] = "Simplifié" Then: [G42] = "ERREUR": [G43] = "ERREUR"
If [g40] = "BNC déclaration contrôlée" And [g41] = "N/A" Then: [G42] = "2035": [G43] = ""
If [g40] = "Non fiscalisé" And [g41] = "Normal" Then: [G42] = "ERREUR": [G43] = "ERREUR"
If [g40] = "Non fiscalisé" And [g41] = "Simplifié" Then: [G42] = "ERREUR": [G43] = "ERREUR"
If [g40] = "Non fiscalisé" And [g41] = "N/A" Then: [G42] = "N/A": [G43] = "N/A"
If [g40] = "" And [g41] = "" Then: [G42] = "": [G43] = ""
Application.ScreenUpdating = True
End Sub

Serge































"Sunburn" a écrit dans le message de news:

> Bonjour,j'ai ce code VBA qui plante,mais je n'arrive pas à identifié le
> problème ...
> il n'y a pas d'autres macro sur mon classeur.
> en fait, j'ai des listes de choix en G40 et G41, et ça doit impliquer des
> réponses en G42 et G43
>
> merci de votre aide.
> YANN
> ----
> Private Sub Worksheet_Change(ByVal Target As Range)
> Set inter = Intersect(Target, Range("G40:G41"))
> If Not inter Is Nothing Then
> DeclarationsAFaire
> End If
> End Sub
>
> Sub DeclarationsAFaire()
> Application.ScreenUpdating = False
> If [g40] = "IS" And [g41] = "Normal" Then: [G42] = "2065": [G43] = "2050"
> If [g40] = "IS" And [g41] = "Simplifié" Then: [G42] = "2065": [G43] = "2033"
> If [g40] = "IS" And [g41] = "N/A" Then: [G42] = "ERREUR": [G43] = "ERREUR"
> If [g40] = "IR" And [g41] = "Normal" Then: [G42] = "2031": [G43] = "2050"
> If [g40] = "IR" And [g41] = "Simplifié" Then: [G42] = "2031": [G43] = "2033"
> If [g40] = "IR" And [g41] = "N/A" Then: [G42] = "ERREUR": [G43] = "ERREUR"
> If [g40] = "BA IR" And [g41] = "Normal" Then: [G42] = "2143": [G43] = "2144"
> If [g40] = "BA IR" And [g41] = "Simplifié" Then: [G42] = "2139": [G43] = ""
> If [g40] = "BA IR" And [g41] = "N/A" Then: [G42] = "ERREUR": [G43] = "ERREUR"
> If [g40] = "BNC spécial" And [g41] = "Normal" Then: [G42] = "ERREUR": [G43]
> = "ERREUR"
> If [g40] = "BNC spécial" And [g41] = "Simplifié" Then: [G42] = "ERREUR":
> [G43] = "ERREUR"
> If [g40] = "BNC spécial" And [g41] = "N/A" Then: [G42] = "Sur la 2042":
> [G43] = ""
> If [g40] = "BNC déclaration contrôlée" And [g41] = "Normal" Then: [G42] > > "ERREUR": [G43] = "ERREUR"
> If [g40] = "BNC déclaration contrôlée" And [g41] = "Simplifié" Then: [G42] > > "ERREUR": [G43] = "ERREUR"
> If [g40] = "BNC déclaration contrôlée" And [g41] = "N/A" Then: [G42] > > "2035": [G43] = ""
> If [g40] = "Non fiscalisé" And [g41] = "Normal" Then: [G42] = "ERREUR":
> [G43] = "ERREUR"
> If [g40] = "Non fiscalisé" And [g41] = "Simplifié" Then: [G42] = "ERREUR":
> [G43] = "ERREUR"
> If [g40] = "Non fiscalisé" And [g41] = "N/A" Then: [G42] = "N/A": [G43] > > "N/A"
> If [g40] = "" And [g41] = "" Then: [G42] = "": [G43] = ""
> End Sub
> -------





Avatar
sunburn
oui, désolé,
ça plante à la ligne qui est concerné par le changement que j'applique en
G40 ou G41.....
par exemple, si je choisi "IS" en G40, il plante à la première ligne de ma
macro :
----- : [G42] = "2065" ------ et ça c'est surligné en jaune.
je comprend pas.
si je met stop, il plante à stop.... que j'ai rajouté.
merci de ton aide.
YANN


"michdenis" a écrit :

En dessous de cette ligne
Private Sub Worksheet_Change(ByVal Target As Range)

tu ajoutes :
Stop

et tu exécutes toute ta macro en pas à pas.
ça devrait te permettre la ligne de code problématique
peut être pourrons-nous t'aider !



"Sunburn" a écrit dans le message de groupe de
discussion :
Bonjour,j'ai ce code VBA qui plante,mais je n'arrive pas à identifié le
problème ...
il n'y a pas d'autres macro sur mon classeur.
en fait, j'ai des listes de choix en G40 et G41, et ça doit impliquer des
réponses en G42 et G43

merci de votre aide.
YANN
----
Private Sub Worksheet_Change(ByVal Target As Range)
Set inter = Intersect(Target, Range("G40:G41"))
If Not inter Is Nothing Then
DeclarationsAFaire
End If
End Sub

Sub DeclarationsAFaire()
Application.ScreenUpdating = False
If [g40] = "IS" And [g41] = "Normal" Then: [G42] = "2065": [G43] = "2050"
If [g40] = "IS" And [g41] = "Simplifié" Then: [G42] = "2065": [G43] = "2033"
If [g40] = "IS" And [g41] = "N/A" Then: [G42] = "ERREUR": [G43] = "ERREUR"
If [g40] = "IR" And [g41] = "Normal" Then: [G42] = "2031": [G43] = "2050"
If [g40] = "IR" And [g41] = "Simplifié" Then: [G42] = "2031": [G43] = "2033"
If [g40] = "IR" And [g41] = "N/A" Then: [G42] = "ERREUR": [G43] = "ERREUR"
If [g40] = "BA IR" And [g41] = "Normal" Then: [G42] = "2143": [G43] = "2144"
If [g40] = "BA IR" And [g41] = "Simplifié" Then: [G42] = "2139": [G43] = ""
If [g40] = "BA IR" And [g41] = "N/A" Then: [G42] = "ERREUR": [G43] = "ERREUR"
If [g40] = "BNC spécial" And [g41] = "Normal" Then: [G42] = "ERREUR": [G43]
= "ERREUR"
If [g40] = "BNC spécial" And [g41] = "Simplifié" Then: [G42] = "ERREUR":
[G43] = "ERREUR"
If [g40] = "BNC spécial" And [g41] = "N/A" Then: [G42] = "Sur la 2042":
[G43] = ""
If [g40] = "BNC déclaration contrôlée" And [g41] = "Normal" Then: [G42] > "ERREUR": [G43] = "ERREUR"
If [g40] = "BNC déclaration contrôlée" And [g41] = "Simplifié" Then: [G42] > "ERREUR": [G43] = "ERREUR"
If [g40] = "BNC déclaration contrôlée" And [g41] = "N/A" Then: [G42] > "2035": [G43] = ""
If [g40] = "Non fiscalisé" And [g41] = "Normal" Then: [G42] = "ERREUR":
[G43] = "ERREUR"
If [g40] = "Non fiscalisé" And [g41] = "Simplifié" Then: [G42] = "ERREUR":
[G43] = "ERREUR"
If [g40] = "Non fiscalisé" And [g41] = "N/A" Then: [G42] = "N/A": [G43] > "N/A"
If [g40] = "" And [g41] = "" Then: [G42] = "": [G43] = ""
End Sub
-------



Avatar
michdenis
Dans la fenêtre de l'éditeur de code :
barre de menus / outils / références /
si tu as des références marquées "Manquantes"
tu dois les décocher.


"sunburn" a écrit dans le message de groupe de
discussion :
oui, désolé,
ça plante à la ligne qui est concerné par le changement que j'applique en
G40 ou G41.....
par exemple, si je choisi "IS" en G40, il plante à la première ligne de ma
macro :
----- : [G42] = "2065" ------ et ça c'est surligné en jaune.
je comprend pas.
si je met stop, il plante à stop.... que j'ai rajouté.
merci de ton aide.
YANN


"michdenis" a écrit :

En dessous de cette ligne
Private Sub Worksheet_Change(ByVal Target As Range)

tu ajoutes :
Stop

et tu exécutes toute ta macro en pas à pas.
ça devrait te permettre la ligne de code problématique
peut être pourrons-nous t'aider !



"Sunburn" a écrit dans le message de groupe de
discussion :
Bonjour,j'ai ce code VBA qui plante,mais je n'arrive pas à identifié le
problème ...
il n'y a pas d'autres macro sur mon classeur.
en fait, j'ai des listes de choix en G40 et G41, et ça doit impliquer des
réponses en G42 et G43

merci de votre aide.
YANN
----
Private Sub Worksheet_Change(ByVal Target As Range)
Set inter = Intersect(Target, Range("G40:G41"))
If Not inter Is Nothing Then
DeclarationsAFaire
End If
End Sub

Sub DeclarationsAFaire()
Application.ScreenUpdating = False
If [g40] = "IS" And [g41] = "Normal" Then: [G42] = "2065": [G43] = "2050"
If [g40] = "IS" And [g41] = "Simplifié" Then: [G42] = "2065": [G43] = "2033"
If [g40] = "IS" And [g41] = "N/A" Then: [G42] = "ERREUR": [G43] = "ERREUR"
If [g40] = "IR" And [g41] = "Normal" Then: [G42] = "2031": [G43] = "2050"
If [g40] = "IR" And [g41] = "Simplifié" Then: [G42] = "2031": [G43] = "2033"
If [g40] = "IR" And [g41] = "N/A" Then: [G42] = "ERREUR": [G43] = "ERREUR"
If [g40] = "BA IR" And [g41] = "Normal" Then: [G42] = "2143": [G43] = "2144"
If [g40] = "BA IR" And [g41] = "Simplifié" Then: [G42] = "2139": [G43] = ""
If [g40] = "BA IR" And [g41] = "N/A" Then: [G42] = "ERREUR": [G43] = "ERREUR"
If [g40] = "BNC spécial" And [g41] = "Normal" Then: [G42] = "ERREUR": [G43]
= "ERREUR"
If [g40] = "BNC spécial" And [g41] = "Simplifié" Then: [G42] = "ERREUR":
[G43] = "ERREUR"
If [g40] = "BNC spécial" And [g41] = "N/A" Then: [G42] = "Sur la 2042":
[G43] = ""
If [g40] = "BNC déclaration contrôlée" And [g41] = "Normal" Then: [G42] > "ERREUR": [G43] = "ERREUR"
If [g40] = "BNC déclaration contrôlée" And [g41] = "Simplifié" Then: [G42] > "ERREUR": [G43] = "ERREUR"
If [g40] = "BNC déclaration contrôlée" And [g41] = "N/A" Then: [G42] > "2035": [G43] = ""
If [g40] = "Non fiscalisé" And [g41] = "Normal" Then: [G42] = "ERREUR":
[G43] = "ERREUR"
If [g40] = "Non fiscalisé" And [g41] = "Simplifié" Then: [G42] = "ERREUR":
[G43] = "ERREUR"
If [g40] = "Non fiscalisé" And [g41] = "N/A" Then: [G42] = "N/A": [G43] > "N/A"
If [g40] = "" And [g41] = "" Then: [G42] = "": [G43] = ""
End Sub
-------



Avatar
sunburn
re,
oui, mais tu as changé quoi par rapport à ce que j'avais dans le code ??
et la liste en dur dans la feuille, c'est nécessaire ???
merci

"garnote" a écrit :

Bonjour,

Regarde à cette adresse :

http://www.cijoint.fr/cjlink.php?file=cj200902/cijkNlkl5H.xls


Cela te convient-il ?

Serge









"Sunburn" a écrit dans le message de news:

> Bonjour,j'ai ce code VBA qui plante,mais je n'arrive pas à identifié le
> problème ...
> il n'y a pas d'autres macro sur mon classeur.
> en fait, j'ai des listes de choix en G40 et G41, et ça doit impliquer des
> réponses en G42 et G43
>
> merci de votre aide.
> YANN
> ----
> Private Sub Worksheet_Change(ByVal Target As Range)
> Set inter = Intersect(Target, Range("G40:G41"))
> If Not inter Is Nothing Then
> DeclarationsAFaire
> End If
> End Sub
>
> Sub DeclarationsAFaire()
> Application.ScreenUpdating = False
> If [g40] = "IS" And [g41] = "Normal" Then: [G42] = "2065": [G43] = "2050"
> If [g40] = "IS" And [g41] = "Simplifié" Then: [G42] = "2065": [G43] = "2033"
> If [g40] = "IS" And [g41] = "N/A" Then: [G42] = "ERREUR": [G43] = "ERREUR"
> If [g40] = "IR" And [g41] = "Normal" Then: [G42] = "2031": [G43] = "2050"
> If [g40] = "IR" And [g41] = "Simplifié" Then: [G42] = "2031": [G43] = "2033"
> If [g40] = "IR" And [g41] = "N/A" Then: [G42] = "ERREUR": [G43] = "ERREUR"
> If [g40] = "BA IR" And [g41] = "Normal" Then: [G42] = "2143": [G43] = "2144"
> If [g40] = "BA IR" And [g41] = "Simplifié" Then: [G42] = "2139": [G43] = ""
> If [g40] = "BA IR" And [g41] = "N/A" Then: [G42] = "ERREUR": [G43] = "ERREUR"
> If [g40] = "BNC spécial" And [g41] = "Normal" Then: [G42] = "ERREUR": [G43]
> = "ERREUR"
> If [g40] = "BNC spécial" And [g41] = "Simplifié" Then: [G42] = "ERREUR":
> [G43] = "ERREUR"
> If [g40] = "BNC spécial" And [g41] = "N/A" Then: [G42] = "Sur la 2042":
> [G43] = ""
> If [g40] = "BNC déclaration contrôlée" And [g41] = "Normal" Then: [G42] > > "ERREUR": [G43] = "ERREUR"
> If [g40] = "BNC déclaration contrôlée" And [g41] = "Simplifié" Then: [G42] > > "ERREUR": [G43] = "ERREUR"
> If [g40] = "BNC déclaration contrôlée" And [g41] = "N/A" Then: [G42] > > "2035": [G43] = ""
> If [g40] = "Non fiscalisé" And [g41] = "Normal" Then: [G42] = "ERREUR":
> [G43] = "ERREUR"
> If [g40] = "Non fiscalisé" And [g41] = "Simplifié" Then: [G42] = "ERREUR":
> [G43] = "ERREUR"
> If [g40] = "Non fiscalisé" And [g41] = "N/A" Then: [G42] = "N/A": [G43] > > "N/A"
> If [g40] = "" And [g41] = "" Then: [G42] = "": [G43] = ""
> End Sub
> -------





Avatar
sunburn
c'est OK !!!!!!j'avais tout mis dans la feuille, j'avais pas mis la macro
dans un module.....
merci.
YANN

"michdenis" a écrit :

En dessous de cette ligne
Private Sub Worksheet_Change(ByVal Target As Range)

tu ajoutes :
Stop

et tu exécutes toute ta macro en pas à pas.
ça devrait te permettre la ligne de code problématique
peut être pourrons-nous t'aider !



"Sunburn" a écrit dans le message de groupe de
discussion :
Bonjour,j'ai ce code VBA qui plante,mais je n'arrive pas à identifié le
problème ...
il n'y a pas d'autres macro sur mon classeur.
en fait, j'ai des listes de choix en G40 et G41, et ça doit impliquer des
réponses en G42 et G43

merci de votre aide.
YANN
----
Private Sub Worksheet_Change(ByVal Target As Range)
Set inter = Intersect(Target, Range("G40:G41"))
If Not inter Is Nothing Then
DeclarationsAFaire
End If
End Sub

Sub DeclarationsAFaire()
Application.ScreenUpdating = False
If [g40] = "IS" And [g41] = "Normal" Then: [G42] = "2065": [G43] = "2050"
If [g40] = "IS" And [g41] = "Simplifié" Then: [G42] = "2065": [G43] = "2033"
If [g40] = "IS" And [g41] = "N/A" Then: [G42] = "ERREUR": [G43] = "ERREUR"
If [g40] = "IR" And [g41] = "Normal" Then: [G42] = "2031": [G43] = "2050"
If [g40] = "IR" And [g41] = "Simplifié" Then: [G42] = "2031": [G43] = "2033"
If [g40] = "IR" And [g41] = "N/A" Then: [G42] = "ERREUR": [G43] = "ERREUR"
If [g40] = "BA IR" And [g41] = "Normal" Then: [G42] = "2143": [G43] = "2144"
If [g40] = "BA IR" And [g41] = "Simplifié" Then: [G42] = "2139": [G43] = ""
If [g40] = "BA IR" And [g41] = "N/A" Then: [G42] = "ERREUR": [G43] = "ERREUR"
If [g40] = "BNC spécial" And [g41] = "Normal" Then: [G42] = "ERREUR": [G43]
= "ERREUR"
If [g40] = "BNC spécial" And [g41] = "Simplifié" Then: [G42] = "ERREUR":
[G43] = "ERREUR"
If [g40] = "BNC spécial" And [g41] = "N/A" Then: [G42] = "Sur la 2042":
[G43] = ""
If [g40] = "BNC déclaration contrôlée" And [g41] = "Normal" Then: [G42] > "ERREUR": [G43] = "ERREUR"
If [g40] = "BNC déclaration contrôlée" And [g41] = "Simplifié" Then: [G42] > "ERREUR": [G43] = "ERREUR"
If [g40] = "BNC déclaration contrôlée" And [g41] = "N/A" Then: [G42] > "2035": [G43] = ""
If [g40] = "Non fiscalisé" And [g41] = "Normal" Then: [G42] = "ERREUR":
[G43] = "ERREUR"
If [g40] = "Non fiscalisé" And [g41] = "Simplifié" Then: [G42] = "ERREUR":
[G43] = "ERREUR"
If [g40] = "Non fiscalisé" And [g41] = "N/A" Then: [G42] = "N/A": [G43] > "N/A"
If [g40] = "" And [g41] = "" Then: [G42] = "": [G43] = ""
End Sub
-------



Avatar
sunburn
c'est OK !!!!!!j'avais tout mis dans la feuille, j'avais pas mis la macro
dans un module.....
merci.
YANN

"garnote" a écrit :

Bonjour,

Regarde à cette adresse :

http://www.cijoint.fr/cjlink.php?file=cj200902/cijkNlkl5H.xls


Cela te convient-il ?

Serge









"Sunburn" a écrit dans le message de news:

> Bonjour,j'ai ce code VBA qui plante,mais je n'arrive pas à identifié le
> problème ...
> il n'y a pas d'autres macro sur mon classeur.
> en fait, j'ai des listes de choix en G40 et G41, et ça doit impliquer des
> réponses en G42 et G43
>
> merci de votre aide.
> YANN
> ----
> Private Sub Worksheet_Change(ByVal Target As Range)
> Set inter = Intersect(Target, Range("G40:G41"))
> If Not inter Is Nothing Then
> DeclarationsAFaire
> End If
> End Sub
>
> Sub DeclarationsAFaire()
> Application.ScreenUpdating = False
> If [g40] = "IS" And [g41] = "Normal" Then: [G42] = "2065": [G43] = "2050"
> If [g40] = "IS" And [g41] = "Simplifié" Then: [G42] = "2065": [G43] = "2033"
> If [g40] = "IS" And [g41] = "N/A" Then: [G42] = "ERREUR": [G43] = "ERREUR"
> If [g40] = "IR" And [g41] = "Normal" Then: [G42] = "2031": [G43] = "2050"
> If [g40] = "IR" And [g41] = "Simplifié" Then: [G42] = "2031": [G43] = "2033"
> If [g40] = "IR" And [g41] = "N/A" Then: [G42] = "ERREUR": [G43] = "ERREUR"
> If [g40] = "BA IR" And [g41] = "Normal" Then: [G42] = "2143": [G43] = "2144"
> If [g40] = "BA IR" And [g41] = "Simplifié" Then: [G42] = "2139": [G43] = ""
> If [g40] = "BA IR" And [g41] = "N/A" Then: [G42] = "ERREUR": [G43] = "ERREUR"
> If [g40] = "BNC spécial" And [g41] = "Normal" Then: [G42] = "ERREUR": [G43]
> = "ERREUR"
> If [g40] = "BNC spécial" And [g41] = "Simplifié" Then: [G42] = "ERREUR":
> [G43] = "ERREUR"
> If [g40] = "BNC spécial" And [g41] = "N/A" Then: [G42] = "Sur la 2042":
> [G43] = ""
> If [g40] = "BNC déclaration contrôlée" And [g41] = "Normal" Then: [G42] > > "ERREUR": [G43] = "ERREUR"
> If [g40] = "BNC déclaration contrôlée" And [g41] = "Simplifié" Then: [G42] > > "ERREUR": [G43] = "ERREUR"
> If [g40] = "BNC déclaration contrôlée" And [g41] = "N/A" Then: [G42] > > "2035": [G43] = ""
> If [g40] = "Non fiscalisé" And [g41] = "Normal" Then: [G42] = "ERREUR":
> [G43] = "ERREUR"
> If [g40] = "Non fiscalisé" And [g41] = "Simplifié" Then: [G42] = "ERREUR":
> [G43] = "ERREUR"
> If [g40] = "Non fiscalisé" And [g41] = "N/A" Then: [G42] = "N/A": [G43] > > "N/A"
> If [g40] = "" And [g41] = "" Then: [G42] = "": [G43] = ""
> End Sub
> -------





Avatar
sunburn
non...
en fait, le problème vient que mon onglet est verouillé.....
comment remédier à ce pb ??
Merci.
YANN

"garnote" a écrit :

Bonjour,

Regarde à cette adresse :

http://www.cijoint.fr/cjlink.php?file=cj200902/cijkNlkl5H.xls


Cela te convient-il ?

Serge









"Sunburn" a écrit dans le message de news:

> Bonjour,j'ai ce code VBA qui plante,mais je n'arrive pas à identifié le
> problème ...
> il n'y a pas d'autres macro sur mon classeur.
> en fait, j'ai des listes de choix en G40 et G41, et ça doit impliquer des
> réponses en G42 et G43
>
> merci de votre aide.
> YANN
> ----
> Private Sub Worksheet_Change(ByVal Target As Range)
> Set inter = Intersect(Target, Range("G40:G41"))
> If Not inter Is Nothing Then
> DeclarationsAFaire
> End If
> End Sub
>
> Sub DeclarationsAFaire()
> Application.ScreenUpdating = False
> If [g40] = "IS" And [g41] = "Normal" Then: [G42] = "2065": [G43] = "2050"
> If [g40] = "IS" And [g41] = "Simplifié" Then: [G42] = "2065": [G43] = "2033"
> If [g40] = "IS" And [g41] = "N/A" Then: [G42] = "ERREUR": [G43] = "ERREUR"
> If [g40] = "IR" And [g41] = "Normal" Then: [G42] = "2031": [G43] = "2050"
> If [g40] = "IR" And [g41] = "Simplifié" Then: [G42] = "2031": [G43] = "2033"
> If [g40] = "IR" And [g41] = "N/A" Then: [G42] = "ERREUR": [G43] = "ERREUR"
> If [g40] = "BA IR" And [g41] = "Normal" Then: [G42] = "2143": [G43] = "2144"
> If [g40] = "BA IR" And [g41] = "Simplifié" Then: [G42] = "2139": [G43] = ""
> If [g40] = "BA IR" And [g41] = "N/A" Then: [G42] = "ERREUR": [G43] = "ERREUR"
> If [g40] = "BNC spécial" And [g41] = "Normal" Then: [G42] = "ERREUR": [G43]
> = "ERREUR"
> If [g40] = "BNC spécial" And [g41] = "Simplifié" Then: [G42] = "ERREUR":
> [G43] = "ERREUR"
> If [g40] = "BNC spécial" And [g41] = "N/A" Then: [G42] = "Sur la 2042":
> [G43] = ""
> If [g40] = "BNC déclaration contrôlée" And [g41] = "Normal" Then: [G42] > > "ERREUR": [G43] = "ERREUR"
> If [g40] = "BNC déclaration contrôlée" And [g41] = "Simplifié" Then: [G42] > > "ERREUR": [G43] = "ERREUR"
> If [g40] = "BNC déclaration contrôlée" And [g41] = "N/A" Then: [G42] > > "2035": [G43] = ""
> If [g40] = "Non fiscalisé" And [g41] = "Normal" Then: [G42] = "ERREUR":
> [G43] = "ERREUR"
> If [g40] = "Non fiscalisé" And [g41] = "Simplifié" Then: [G42] = "ERREUR":
> [G43] = "ERREUR"
> If [g40] = "Non fiscalisé" And [g41] = "N/A" Then: [G42] = "N/A": [G43] > > "N/A"
> If [g40] = "" And [g41] = "" Then: [G42] = "": [G43] = ""
> End Sub
> -------





1 2