Code VBA erreur????

[TheProdigy]

bONSOIR 0 TOUS EXISTE-T-IL ERREUR DANS LE CODE SUIVANT
mERCI

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cpt As Integer

For Cpt = 1 To 37
If Target.Address = "$V$" & CStr(Cpt) Then
Range("P" & CStr(Cpt + 6)) = Range("S" & CStr(Cpt + 6))
Range("S" & CStr(Cpt + 6)) = Range("V" & CStr(Cpt))
ElseIf Target.Address = "$X$" & CStr(Cpt) Then
Range("P" & CStr(Cpt + 44)) = Range("S" & CStr(Cpt + 44))
Range("S" & CStr(Cpt + 44)) = Range("X" & CStr(Cpt))
End If
Next Cpt
End Sub

Merci

 

[PascalXLD]

Re : Code VBA erreur????

Bonsoir

Le problème c'est surtout que ton evenemetn change va se redeclencher dès

Range("P" & CStr(Cpt + 6)) = Range("S" & CStr(Cpt + 6))

 

[pierrejean]

Re : Code VBA erreur????

bonsoir

A priori il n'y a pas d'erreur

peux-t-on savoir pourquoi tu poses cette question ?

 

[TheProdigy]

Re : Code VBA erreur????

Parceque mon projet lors de l'ouverture me signale erreur d'execution 1004 à l'ouverture.
Merci

 

[TheProdigy]

Re : Code VBA erreur????

Je vais poser la question autrement est-ce c'est l'équivalent du code suivant?

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$V$1" Then
Range("P7") = Range("S7")
Range("S7") = Range("V1")
End If
If Target.Address = "$V$2" Then
Range("P8") = Range("S8")
Range("S8") = Range("V2")
End If
If Target.Address = "$V$3" Then
Range("P9") = Range("S9")
Range("S9") = Range("V3")
End If
If Target.Address = "$V$4" Then
Range("P10") = Range("S10")
Range("S10") = Range("V4")
End If
If Target.Address = "$V$5" Then
Range("P11") = Range("S11")
Range("S11") = Range("V5")
End If
If Target.Address = "$V$6" Then
Range("P12") = Range("S12")
Range("S12") = Range("V6")
End If
If Target.Address = "$V$7" Then
Range("P13") = Range("S13")
Range("S13") = Range("V7")
End If
If Target.Address = "$V$8" Then
Range("P14") = Range("S14")
Range("S14") = Range("V8")
End If
If Target.Address = "$V$9" Then
Range("P15") = Range("S15")
Range("S15") = Range("V9")
End If
If Target.Address = "$V$10" Then
Range("P16") = Range("S16")
Range("S16") = Range("V10")
End If
If Target.Address = "$V$11" Then
Range("P17") = Range("S17")
Range("S17") = Range("V11")
End If
If Target.Address = "$V$12" Then
Range("P18") = Range("S18")
Range("S18") = Range("V12")
End If
If Target.Address = "$V$13" Then
Range("P19") = Range("S19")
Range("S19") = Range("V13")
End If
If Target.Address = "$V$14" Then
Range("P20") = Range("S20")
Range("S20") = Range("V14")
End If
If Target.Address = "$V$15" Then
Range("P21") = Range("S21")
Range("S21") = Range("V15")
End If
If Target.Address = "$V$16" Then
Range("P22") = Range("S22")
Range("S22") = Range("V16")
End If
If Target.Address = "$V$17" Then
Range("P23") = Range("S23")
Range("S23") = Range("V17")
End If
If Target.Address = "$V$18" Then
Range("P24") = Range("S24")
Range("S24") = Range("V18")
End If
If Target.Address = "$V$19" Then
Range("P25") = Range("S25")
Range("S25") = Range("V19")
End If
If Target.Address = "$V$20" Then
Range("P26") = Range("S26")
Range("S26") = Range("V20")
End If
If Target.Address = "$V$21" Then
Range("P27") = Range("S27")
Range("S27") = Range("V21")
End If
If Target.Address = "$V$22" Then
Range("P28") = Range("S28")
Range("S28") = Range("V22")
End If
If Target.Address = "$V$23" Then
Range("P29") = Range("S29")
Range("S29") = Range("V23")
End If
If Target.Address = "$V$24" Then
Range("P30") = Range("S30")
Range("S30") = Range("V24")
End If
If Target.Address = "$V$25" Then
Range("P31") = Range("S31")
Range("S31") = Range("V25")
End If
If Target.Address = "$V$26" Then
Range("P32") = Range("S32")
Range("S32") = Range("V26")
End If
If Target.Address = "$V$27" Then
Range("P33") = Range("S33")
Range("S33") = Range("V27")
End If
If Target.Address = "$V$28" Then
Range("P34") = Range("S34")
Range("S34") = Range("V28")
End If
If Target.Address = "$V$29" Then
Range("P35") = Range("S35")
Range("S35") = Range("V29")
End If
If Target.Address = "$V$30" Then
Range("P36") = Range("S36")
Range("S36") = Range("V30")
End If
If Target.Address = "$V$31" Then
Range("P37") = Range("S37")
Range("S37") = Range("V31")
End If
If Target.Address = "$V$32" Then
Range("P38") = Range("S38")
Range("S38") = Range("V32")
End If
If Target.Address = "$V$33" Then
Range("P39") = Range("S39")
Range("S39") = Range("V33")
End If
If Target.Address = "$V$34" Then
Range("P40") = Range("S40")
Range("S40") = Range("V34")
End If
If Target.Address = "$V$35" Then
Range("P41") = Range("S41")
Range("S41") = Range("V35")
End If
'****************Maintenant la variation des plus values****************
If Target.Address = "$X$1" Then
Range("P43") = Range("S43")
Range("S43") = Range("X1")
End If
If Target.Address = "$X$2" Then
Range("P44") = Range("S44")
Range("S44") = Range("X2")
End If
If Target.Address = "$X$3" Then
Range("P45") = Range("S45")
Range("S45") = Range("X3")
End If
If Target.Address = "$X$4" Then
Range("P46") = Range("S46")
Range("S46") = Range("X4")
End If
If Target.Address = "$X$5" Then
Range("P47") = Range("S47")
Range("S47") = Range("X5")
End If
If Target.Address = "$X$6" Then
Range("P48") = Range("S48")
Range("S48") = Range("X6")
End If
If Target.Address = "$X$7" Then
Range("P49") = Range("S49")
Range("S49") = Range("X7")
End If
If Target.Address = "$X$8" Then
Range("P50") = Range("S50")
Range("S50") = Range("X8")
End If
If Target.Address = "$X$9" Then
Range("P51") = Range("S51")
Range("S51") = Range("X9")
End If
If Target.Address = "$X$10" Then
Range("P52") = Range("S52")
Range("S52") = Range("X10")
End If
If Target.Address = "$X$11" Then
Range("P53") = Range("S53")
Range("S53") = Range("X11")
End If
If Target.Address = "$X$12" Then
Range("P54") = Range("S54")
Range("S54") = Range("X12")
End If
If Target.Address = "$X$13" Then
Range("P55") = Range("S55")
Range("S55") = Range("X13")
End If
If Target.Address = "$X$14" Then
Range("P56") = Range("S56")
Range("S56") = Range("X14")
End If
If Target.Address = "$X$15" Then
Range("P57") = Range("S57")
Range("S57") = Range("X15")
End If
If Target.Address = "$X$16" Then
Range("P58") = Range("S58")
Range("S58") = Range("X16")
End If
If Target.Address = "$X$17" Then
Range("P59") = Range("S59")
Range("S59") = Range("X17")
End If
If Target.Address = "$X$18" Then
Range("P60") = Range("S60")
Range("S60") = Range("X18")
End If
If Target.Address = "$X$19" Then
Range("P61") = Range("S61")
Range("S61") = Range("X19")
End If
If Target.Address = "$X$20" Then
Range("P62") = Range("S62")
Range("S62") = Range("X20")
End If
If Target.Address = "$X$21" Then
Range("P63") = Range("S63")
Range("S63") = Range("X21")
End If
If Target.Address = "$X$22" Then
Range("P64") = Range("S64")
Range("S64") = Range("X22")
End If
If Target.Address = "$X$23" Then
Range("P65") = Range("S65")
Range("S65") = Range("X23")
End If
If Target.Address = "$X$24" Then
Range("P66") = Range("S66")
Range("S66") = Range("X24")
End If
If Target.Address = "$X$25" Then
Range("P67") = Range("S67")
Range("S67") = Range("X25")
End If
If Target.Address = "$X$26" Then
Range("P68") = Range("S68")
Range("S68") = Range("X26")
End If
If Target.Address = "$X$27" Then
Range("P69") = Range("S69")
Range("S69") = Range("X27")
End If
If Target.Address = "$X$28" Then
Range("P70") = Range("S70")
Range("S70") = Range("X28")
End If
If Target.Address = "$X$29" Then
Range("P71") = Range("S71")
Range("S71") = Range("X29")
End If
If Target.Address = "$X$30" Then
Range("P72") = Range("S72")
Range("S72") = Range("X30")
End If
If Target.Address = "$X$31" Then
Range("P73") = Range("S73")
Range("S73") = Range("X31")
End If
If Target.Address = "$X$32" Then
Range("P74") = Range("S74")
Range("S74") = Range("X32")
End If
If Target.Address = "$X$33" Then
Range("P75") = Range("S75")
Range("S75") = Range("X33")
End If
If Target.Address = "$X$34" Then
Range("P76") = Range("S76")
Range("S76") = Range("X34")
End If
If Target.Address = "$X$35" Then
Range("P77") = Range("S77")
Range("S77") = Range("X35")
End If
End Sub

 

[wilfried_42]

Re : Code VBA erreur????

bonjour adil, pierrejean pascal

meme reponse que pascal
en modifiant un cellule, tu relance systematiquement la procedure qui se relance en boucle et encore en boule

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cpt As Integer
application.enableevents = not application.enabledevents
For Cpt = 1 To 37
If Target.Address = "$V$" & CStr(Cpt) Then
Range("P" & CStr(Cpt + 6)) = Range("S" & CStr(Cpt + 6))
Range("S" & CStr(Cpt + 6)) = Range("V" & CStr(Cpt))
ElseIf Target.Address = "$X$" & CStr(Cpt) Then
Range("P" & CStr(Cpt + 44)) = Range("S" & CStr(Cpt + 44))
Range("S" & CStr(Cpt + 44)) = Range("X" & CStr(Cpt))
End If
Next Cpt
application.enableevents = not application.enabledevents
End Sub

essaie en ajoutant les deux lignes en rouge

 

[TheProdigy]

Re : Code VBA erreur????

Merci pour votre expertise et votre technicité je vais m'en aller et l'essayer ches moi ce soir. Mais vous me devez une petite explication demain j'espère.
Car je ne suis pas de votre niveau 🙂 je suis trés débutant.

A plus Bye

 

[pierrejean]

Re : Code VBA erreur????

Re

J'ai testé ta premiere ecriture et n'ai constaté aucun problème

A mon avis ton erreur 1004 vient d'ailleurs