Simplification code VBA

TheProdigy

TheProdigy

XLDnaute Impliqué
Bonjour à tous,

Existe-t-il un moyen pour simplifier le 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
 
Trier par date Trier par votes
B

Banzai64

XLDnaute Accro
Re : Simplification code VBA

Bonjour

pas testé

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("V1:V35"), Target) Is Nothing Then
  Range("P" & Target.Row + 6) = Range("S" & Target.Row + 6)
  Range("S" & Target.Row + 6) = Target
ElseIf Not Intersect(Range("X1:X35"), Target) Is Nothing Then
  Range("P" & Target.Row + 42) = Range("S" & Target.Row + 42)
  Range("S" & Target.Row + 42) = Target
End If

End Sub
 
Vote positif 0 Vote négatif
M

mécano41

XLDnaute Accro
Re : Simplification code VBA

Bonjour,

Tu peux aussi essayer cela :

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

For Cpt = 1 To 35
    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))
    End If
Next Cpt
End Sub

Cordialement
 
Vote positif 0 Vote négatif

Discussions similaires

R
  • Question
XL 2021 Optimisation Code VBA
Réponses
10
Affichages
373
Dranreb
Dranreb
K
  • Résolu(e)
XL 2013 Amélioration code VBA
Réponses
6
Affichages
325
vgendron
vgendron
M
  • Question
XL 2019 Code VBA
Réponses
12
Affichages
573
MarieLN
M
G
  • Résolu(e)
XL 2019 Consigne non suivi dans une suite de ElseIf
Réponses
8
Affichages
393
Gstn
G
H
  • Question
Microsoft 365 Oups ! Quelque chose ne va pas a mon code vba.
Réponses
13
Affichages
716
hlawson1
H
J
  • Question
XL 2016 [RESOLU] Avis Code VBA excel
Réponses
9
Affichages
739
jabenj
J
D
  • Question
XL 2016 Button copier coller cellules indépendantes VBA autre feuille
Réponses
7
Affichages
947
vgendron
vgendron
L
  • Question
XL 2019 code de destockage
Réponses
4
Affichages
848
Lune999
L
D
  • Question
XL 2016 Formulaire permettant de masquer et/ou d'afficher une ligne VBA en fonction d'un caractère et/ou d'une couleur
Réponses
9
Affichages
1 K
doby
D
M
  • Question
XL 2016 Optimiser code VBA
Réponses
1
Affichages
673
herve62
herve62

Membres actuellement en ligne

18 membres et 246 invités.

Partager cette page

Tutos vidéos

Power Query : Ajouter des requêtes (Excel et Power BI)
Créez votre première fonction LAMBDA dans Excel en 3 étapes!
LAMBDA, l’arme secrète de Microsoft pour Excel !
Office Insider - Découvrez les nouveautés Excel avant tout le monde

Statistiques des forums

Discussions
312 232
Messages
2 086 461
Membres
103 220
dernier inscrit
samira2024