Plantage dans le code

Le
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
-
Vidéos High-Tech et Jeu Vidéo
Téléchargements
Vos réponses Page 1 / 2
Gagnez chaque mois un abonnement Premium avec GNT : Inscrivez-vous !
Trier par : date / pertinence
michdenis
Le #18600251
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" 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
-------
garnote
Le #18600241
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"
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
-------


garnote
Le #18600411
Bonjour,

Regarde à cette adresse :

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


Cela te convient-il ?

Serge









"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
-------


Sunburn
Le #18600401
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"
> 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
> -------





sunburn
Le #18600391
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" 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
-------



michdenis
Le #18600371
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" 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" 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
-------



sunburn
Le #18600521
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"
> 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
> -------





sunburn
Le #18600511
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" 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
-------



sunburn
Le #18600841
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"
> 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
> -------





sunburn
Le #18600501
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"
> 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
> -------





Publicité
Poster une réponse
Anonyme