D
Domi_d
Guest
Bonjour,
Pour simplifier ce code :
If Not Intersect(Target, Range('C2')) Is Nothing Then
Range('C2') = 3
Range('C3') = Empty
Range('F3') = Range('B2')
ElseIf Not Intersect(Target, Range('C3')) Is Nothing Then
Range('C3') = 3
Range('C2') = Empty
Range('F3') = Range('B3')
End If
If Not Intersect(Target, Range('C4')) Is Nothing Then
Range('C4') = 3
Range('C5') = Empty
Range('F4') = Range('B4')
ElseIf Not Intersect(Target, Range('C5')) Is Nothing Then
Range('C5') = 3
Range('C4') = Empty
Range('F4') = Range('B5')
End If
If Not Intersect(Target, Range('C6')) Is Nothing Then
Range('C6') = 3
Range('C7') = Empty
Range('F7') = Range('B6')
ElseIf Not Intersect(Target, Range('C7')) Is Nothing Then
Range('C7') = 3
Range('C6') = Empty
Range('F7') = Range('B7')
End If
If Not Intersect(Target, Range('C8')) Is Nothing Then
Range('C8') = 3
Range('C9') = Empty
Range('F8') = Range('B8')
ElseIf Not Intersect(Target, Range('C9')) Is Nothing Then
Range('C9') = 3
Range('C8') = Empty
Range('F8') = Range('B9')
End If
If Not Intersect(Target, Range('C10')) Is Nothing Then
Range('C10') = 3
Range('C11') = Empty
Range('F11') = Range('B10')
ElseIf Not Intersect(Target, Range('C11')) Is Nothing Then
Range('C11') = 3
Range('C10') = Empty
Range('F11') = Range('B11')
End If
If Not Intersect(Target, Range('C12')) Is Nothing Then
Range('C12') = 3
Range('C13') = Empty
Range('F12') = Range('B12')
ElseIf Not Intersect(Target, Range('C13')) Is Nothing Then
Range('C13') = 3
Range('C12') = Empty
Range('F12') = Range('B13')
End If
If Not Intersect(Target, Range('C14')) Is Nothing Then
Range('C14') = 3
Range('C15') = Empty
Range('F15') = Range('B14')
ElseIf Not Intersect(Target, Range('C15')) Is Nothing Then
Range('C15') = 3
Range('C14') = Empty
Range('F15') = Range('B15')
End If
If Not Intersect(Target, Range('C16')) Is Nothing Then
Range('C16') = 3
Range('C17') = Empty
Range('F16') = Range('B16')
ElseIf Not Intersect(Target, Range('C17')) Is Nothing Then
Range('C17') = 3
Range('C16') = Empty
Range('F16') = Range('B17')
End If
If Not Intersect(Target, Range('C18')) Is Nothing Then
Range('C18') = 3
Range('C19') = Empty
Range('F19') = Range('B18')
ElseIf Not Intersect(Target, Range('C19')) Is Nothing Then
Range('C19') = 3
Range('C18') = Empty
Range('F19') = Range('B19')
End If
If Not Intersect(Target, Range('C20')) Is Nothing Then
Range('C20') = 3
Range('C21') = Empty
Range('F20') = Range('B20')
ElseIf Not Intersect(Target, Range('C21')) Is Nothing Then
Range('C21') = 3
Range('C20') = Empty
Range('F20') = Range('B21')
End If
If Not Intersect(Target, Range('C22')) Is Nothing Then
Range('C22') = 3
Range('C23') = Empty
Range('F23') = Range('B22')
ElseIf Not Intersect(Target, Range('C23')) Is Nothing Then
Range('C23') = 3
Range('C22') = Empty
Range('F23') = Range('B23')
End If
If Not Intersect(Target, Range('C24')) Is Nothing Then
Range('C24') = 3
Range('C25') = Empty
Range('F24') = Range('B24')
ElseIf Not Intersect(Target, Range('C25')) Is Nothing Then
Range('C25') = 3
Range('C24') = Empty
Range('F24') = Range('B25')
End If
If Not Intersect(Target, Range('C26')) Is Nothing Then
Range('C26') = 3
Range('C27') = Empty
Range('F27') = Range('B26')
ElseIf Not Intersect(Target, Range('C27')) Is Nothing Then
Range('C27') = 3
Range('C26') = Empty
Range('F27') = Range('B27')
End If
If Not Intersect(Target, Range('C28')) Is Nothing Then
Range('C28') = 3
Range('C29') = Empty
Range('F28') = Range('B28')
ElseIf Not Intersect(Target, Range('C29')) Is Nothing Then
Range('C29') = 3
Range('C28') = Empty
Range('F28') = Range('B29')
End If
If Not Intersect(Target, Range('C30')) Is Nothing Then
Range('C30') = 3
Range('C31') = Empty
Range('F31') = Range('B30')
ElseIf Not Intersect(Target, Range('C31')) Is Nothing Then
Range('C31') = 3
Range('C30') = Empty
Range('F31') = Range('B31')
End If
If Not Intersect(Target, Range('C32')) Is Nothing Then
Range('C32') = 3
Range('C33') = Empty
Range('F32') = Range('B32')
ElseIf Not Intersect(Target, Range('C33')) Is Nothing Then
Range('C33') = 3
Range('C32') = Empty
Range('F32') = Range('B33')
End If
...j'ai simplifié par ça :
Dim i, j
j = 2
For i = 0 To 28 Step 4
If Not Intersect(Target, Range('C' & 2 + i & ':C' & 5 + i)) Is Nothing Then
If Target.Row Mod 2 = 0 Then
Target.Value = 3
Target.Offset(1, 0).Value = Empty
li = Target.Row / 2 + j
Else
Target.Value = 3
Target.Offset(-1, 0).Value = Empty
li = Target.Row \\ 2 + j
End If
Cells(li, 6).Value = Target.Offset(0, -1).Value
End If
j = j + 2
Next i
Par contre, pour quasiment la même chose :
If Not Intersect(Target, Range('C41')) Is Nothing Then
Range('C41') = 3
Range('C42') = Empty
Range('F42') = Range('B41')
ElseIf Not Intersect(Target, Range('C42')) Is Nothing Then
Range('C42') = 3
Range('C41') = Empty
Range('F42') = Range('B42')
End If
If Not Intersect(Target, Range('C43')) Is Nothing Then
Range('C43') = 3
Range('C44') = Empty
Range('F43') = Range('B43')
ElseIf Not Intersect(Target, Range('C44')) Is Nothing Then
Range('C44') = 3
Range('C43') = Empty
Range('F43') = Range('B44')
End If
If Not Intersect(Target, Range('C45')) Is Nothing Then
Range('C45') = 3
Range('C46') = Empty
Range('F46') = Range('B45')
ElseIf Not Intersect(Target, Range('C46')) Is Nothing Then
Range('C46') = 3
Range('C45') = Empty
Range('F46') = Range('B46')
End If
If Not Intersect(Target, Range('C47')) Is Nothing Then
Range('C47') = 3
Range('C48') = Empty
Range('F47') = Range('B47')
ElseIf Not Intersect(Target, Range('C48')) Is Nothing Then
Range('C48') = 3
Range('C47') = Empty
Range('F47') = Range('B48')
End If
If Not Intersect(Target, Range('C49')) Is Nothing Then
Range('C49') = 3
Range('C50') = Empty
Range('F50') = Range('B49')
ElseIf Not Intersect(Target, Range('C50')) Is Nothing Then
Range('C50') = 3
Range('C49') = Empty
Range('F50') = Range('B50')
End If
If Not Intersect(Target, Range('C51')) Is Nothing Then
Range('C51') = 3
Range('C52') = Empty
Range('F51') = Range('B51')
ElseIf Not Intersect(Target, Range('C52')) Is Nothing Then
Range('C52') = 3
Range('C51') = Empty
Range('F51') = Range('B52')
End If
If Not Intersect(Target, Range('C53')) Is Nothing Then
Range('C53') = 3
Range('C54') = Empty
Range('F54') = Range('B53')
ElseIf Not Intersect(Target, Range('C54')) Is Nothing Then
Range('C54') = 3
Range('C53') = Empty
Range('F54') = Range('B54')
End If
If Not Intersect(Target, Range('C55')) Is Nothing Then
Range('C55') = 3
Range('C56') = Empty
Range('F55') = Range('B55')
ElseIf Not Intersect(Target, Range('C56')) Is Nothing Then
Range('C56') = 3
Range('C55') = Empty
Range('F55') = Range('B56')
End If
If Not Intersect(Target, Range('C57')) Is Nothing Then
Range('C57') = 3
Range('C58') = Empty
Range('F58') = Range('B57')
ElseIf Not Intersect(Target, Range('C58')) Is Nothing Then
Range('C58') = 3
Range('C57') = Empty
Range('F58') = Range('B58')
End If
If Not Intersect(Target, Range('C59')) Is Nothing Then
Range('C59') = 3
Range('C60') = Empty
Range('F59') = Range('B59')
ElseIf Not Intersect(Target, Range('C60')) Is Nothing Then
Range('C60') = 3
Range('C59') = Empty
Range('F59') = Range('B60')
End If
If Not Intersect(Target, Range('C61')) Is Nothing Then
Range('C61') = 3
Range('C62') = Empty
Range('F62') = Range('B61')
ElseIf Not Intersect(Target, Range('C62')) Is Nothing Then
Range('C62') = 3
Range('C61') = Empty
Range('F62') = Range('B62')
End If
If Not Intersect(Target, Range('C63')) Is Nothing Then
Range('C63') = 3
Range('C64') = Empty
Range('F63') = Range('B63')
ElseIf Not Intersect(Target, Range('C64')) Is Nothing Then
Range('C64') = 3
Range('C63') = Empty
Range('F63') = Range('B64')
End If
If Not Intersect(Target, Range('C65')) Is Nothing Then
Range('C65') = 3
Range('C66') = Empty
Range('F66') = Range('B65')
ElseIf Not Intersect(Target, Range('C66')) Is Nothing Then
Range('C66') = 3
Range('C65') = Empty
Range('F66') = Range('B66')
End If
If Not Intersect(Target, Range('C67')) Is Nothing Then
Range('C67') = 3
Range('C68') = Empty
Range('F67') = Range('B67')
ElseIf Not Intersect(Target, Range('C68')) Is Nothing Then
Range('C68') = 3
Range('C67') = Empty
Range('F67') = Range('B68')
End If
If Not Intersect(Target, Range('C69')) Is Nothing Then
Range('C69') = 3
Range('C70') = Empty
Range('F70') = Range('B69')
ElseIf Not Intersect(Target, Range('C70')) Is Nothing Then
Range('C70') = 3
Range('C69') = Empty
Range('F70') = Range('B70')
End If
If Not Intersect(Target, Range('C71')) Is Nothing Then
Range('C71') = 3
Range('C72') = Empty
Range('F71') = Range('B71')
ElseIf Not Intersect(Target, Range('C72')) Is Nothing Then
Range('C72') = 3
Range('C71') = Empty
Range('F71') = Range('B72')
End If
..........je sèche !!
Vous voyez la solution ??
----------------
Merci d'avance.
Domi
Pour simplifier ce code :
If Not Intersect(Target, Range('C2')) Is Nothing Then
Range('C2') = 3
Range('C3') = Empty
Range('F3') = Range('B2')
ElseIf Not Intersect(Target, Range('C3')) Is Nothing Then
Range('C3') = 3
Range('C2') = Empty
Range('F3') = Range('B3')
End If
If Not Intersect(Target, Range('C4')) Is Nothing Then
Range('C4') = 3
Range('C5') = Empty
Range('F4') = Range('B4')
ElseIf Not Intersect(Target, Range('C5')) Is Nothing Then
Range('C5') = 3
Range('C4') = Empty
Range('F4') = Range('B5')
End If
If Not Intersect(Target, Range('C6')) Is Nothing Then
Range('C6') = 3
Range('C7') = Empty
Range('F7') = Range('B6')
ElseIf Not Intersect(Target, Range('C7')) Is Nothing Then
Range('C7') = 3
Range('C6') = Empty
Range('F7') = Range('B7')
End If
If Not Intersect(Target, Range('C8')) Is Nothing Then
Range('C8') = 3
Range('C9') = Empty
Range('F8') = Range('B8')
ElseIf Not Intersect(Target, Range('C9')) Is Nothing Then
Range('C9') = 3
Range('C8') = Empty
Range('F8') = Range('B9')
End If
If Not Intersect(Target, Range('C10')) Is Nothing Then
Range('C10') = 3
Range('C11') = Empty
Range('F11') = Range('B10')
ElseIf Not Intersect(Target, Range('C11')) Is Nothing Then
Range('C11') = 3
Range('C10') = Empty
Range('F11') = Range('B11')
End If
If Not Intersect(Target, Range('C12')) Is Nothing Then
Range('C12') = 3
Range('C13') = Empty
Range('F12') = Range('B12')
ElseIf Not Intersect(Target, Range('C13')) Is Nothing Then
Range('C13') = 3
Range('C12') = Empty
Range('F12') = Range('B13')
End If
If Not Intersect(Target, Range('C14')) Is Nothing Then
Range('C14') = 3
Range('C15') = Empty
Range('F15') = Range('B14')
ElseIf Not Intersect(Target, Range('C15')) Is Nothing Then
Range('C15') = 3
Range('C14') = Empty
Range('F15') = Range('B15')
End If
If Not Intersect(Target, Range('C16')) Is Nothing Then
Range('C16') = 3
Range('C17') = Empty
Range('F16') = Range('B16')
ElseIf Not Intersect(Target, Range('C17')) Is Nothing Then
Range('C17') = 3
Range('C16') = Empty
Range('F16') = Range('B17')
End If
If Not Intersect(Target, Range('C18')) Is Nothing Then
Range('C18') = 3
Range('C19') = Empty
Range('F19') = Range('B18')
ElseIf Not Intersect(Target, Range('C19')) Is Nothing Then
Range('C19') = 3
Range('C18') = Empty
Range('F19') = Range('B19')
End If
If Not Intersect(Target, Range('C20')) Is Nothing Then
Range('C20') = 3
Range('C21') = Empty
Range('F20') = Range('B20')
ElseIf Not Intersect(Target, Range('C21')) Is Nothing Then
Range('C21') = 3
Range('C20') = Empty
Range('F20') = Range('B21')
End If
If Not Intersect(Target, Range('C22')) Is Nothing Then
Range('C22') = 3
Range('C23') = Empty
Range('F23') = Range('B22')
ElseIf Not Intersect(Target, Range('C23')) Is Nothing Then
Range('C23') = 3
Range('C22') = Empty
Range('F23') = Range('B23')
End If
If Not Intersect(Target, Range('C24')) Is Nothing Then
Range('C24') = 3
Range('C25') = Empty
Range('F24') = Range('B24')
ElseIf Not Intersect(Target, Range('C25')) Is Nothing Then
Range('C25') = 3
Range('C24') = Empty
Range('F24') = Range('B25')
End If
If Not Intersect(Target, Range('C26')) Is Nothing Then
Range('C26') = 3
Range('C27') = Empty
Range('F27') = Range('B26')
ElseIf Not Intersect(Target, Range('C27')) Is Nothing Then
Range('C27') = 3
Range('C26') = Empty
Range('F27') = Range('B27')
End If
If Not Intersect(Target, Range('C28')) Is Nothing Then
Range('C28') = 3
Range('C29') = Empty
Range('F28') = Range('B28')
ElseIf Not Intersect(Target, Range('C29')) Is Nothing Then
Range('C29') = 3
Range('C28') = Empty
Range('F28') = Range('B29')
End If
If Not Intersect(Target, Range('C30')) Is Nothing Then
Range('C30') = 3
Range('C31') = Empty
Range('F31') = Range('B30')
ElseIf Not Intersect(Target, Range('C31')) Is Nothing Then
Range('C31') = 3
Range('C30') = Empty
Range('F31') = Range('B31')
End If
If Not Intersect(Target, Range('C32')) Is Nothing Then
Range('C32') = 3
Range('C33') = Empty
Range('F32') = Range('B32')
ElseIf Not Intersect(Target, Range('C33')) Is Nothing Then
Range('C33') = 3
Range('C32') = Empty
Range('F32') = Range('B33')
End If
...j'ai simplifié par ça :
Dim i, j
j = 2
For i = 0 To 28 Step 4
If Not Intersect(Target, Range('C' & 2 + i & ':C' & 5 + i)) Is Nothing Then
If Target.Row Mod 2 = 0 Then
Target.Value = 3
Target.Offset(1, 0).Value = Empty
li = Target.Row / 2 + j
Else
Target.Value = 3
Target.Offset(-1, 0).Value = Empty
li = Target.Row \\ 2 + j
End If
Cells(li, 6).Value = Target.Offset(0, -1).Value
End If
j = j + 2
Next i
Par contre, pour quasiment la même chose :
If Not Intersect(Target, Range('C41')) Is Nothing Then
Range('C41') = 3
Range('C42') = Empty
Range('F42') = Range('B41')
ElseIf Not Intersect(Target, Range('C42')) Is Nothing Then
Range('C42') = 3
Range('C41') = Empty
Range('F42') = Range('B42')
End If
If Not Intersect(Target, Range('C43')) Is Nothing Then
Range('C43') = 3
Range('C44') = Empty
Range('F43') = Range('B43')
ElseIf Not Intersect(Target, Range('C44')) Is Nothing Then
Range('C44') = 3
Range('C43') = Empty
Range('F43') = Range('B44')
End If
If Not Intersect(Target, Range('C45')) Is Nothing Then
Range('C45') = 3
Range('C46') = Empty
Range('F46') = Range('B45')
ElseIf Not Intersect(Target, Range('C46')) Is Nothing Then
Range('C46') = 3
Range('C45') = Empty
Range('F46') = Range('B46')
End If
If Not Intersect(Target, Range('C47')) Is Nothing Then
Range('C47') = 3
Range('C48') = Empty
Range('F47') = Range('B47')
ElseIf Not Intersect(Target, Range('C48')) Is Nothing Then
Range('C48') = 3
Range('C47') = Empty
Range('F47') = Range('B48')
End If
If Not Intersect(Target, Range('C49')) Is Nothing Then
Range('C49') = 3
Range('C50') = Empty
Range('F50') = Range('B49')
ElseIf Not Intersect(Target, Range('C50')) Is Nothing Then
Range('C50') = 3
Range('C49') = Empty
Range('F50') = Range('B50')
End If
If Not Intersect(Target, Range('C51')) Is Nothing Then
Range('C51') = 3
Range('C52') = Empty
Range('F51') = Range('B51')
ElseIf Not Intersect(Target, Range('C52')) Is Nothing Then
Range('C52') = 3
Range('C51') = Empty
Range('F51') = Range('B52')
End If
If Not Intersect(Target, Range('C53')) Is Nothing Then
Range('C53') = 3
Range('C54') = Empty
Range('F54') = Range('B53')
ElseIf Not Intersect(Target, Range('C54')) Is Nothing Then
Range('C54') = 3
Range('C53') = Empty
Range('F54') = Range('B54')
End If
If Not Intersect(Target, Range('C55')) Is Nothing Then
Range('C55') = 3
Range('C56') = Empty
Range('F55') = Range('B55')
ElseIf Not Intersect(Target, Range('C56')) Is Nothing Then
Range('C56') = 3
Range('C55') = Empty
Range('F55') = Range('B56')
End If
If Not Intersect(Target, Range('C57')) Is Nothing Then
Range('C57') = 3
Range('C58') = Empty
Range('F58') = Range('B57')
ElseIf Not Intersect(Target, Range('C58')) Is Nothing Then
Range('C58') = 3
Range('C57') = Empty
Range('F58') = Range('B58')
End If
If Not Intersect(Target, Range('C59')) Is Nothing Then
Range('C59') = 3
Range('C60') = Empty
Range('F59') = Range('B59')
ElseIf Not Intersect(Target, Range('C60')) Is Nothing Then
Range('C60') = 3
Range('C59') = Empty
Range('F59') = Range('B60')
End If
If Not Intersect(Target, Range('C61')) Is Nothing Then
Range('C61') = 3
Range('C62') = Empty
Range('F62') = Range('B61')
ElseIf Not Intersect(Target, Range('C62')) Is Nothing Then
Range('C62') = 3
Range('C61') = Empty
Range('F62') = Range('B62')
End If
If Not Intersect(Target, Range('C63')) Is Nothing Then
Range('C63') = 3
Range('C64') = Empty
Range('F63') = Range('B63')
ElseIf Not Intersect(Target, Range('C64')) Is Nothing Then
Range('C64') = 3
Range('C63') = Empty
Range('F63') = Range('B64')
End If
If Not Intersect(Target, Range('C65')) Is Nothing Then
Range('C65') = 3
Range('C66') = Empty
Range('F66') = Range('B65')
ElseIf Not Intersect(Target, Range('C66')) Is Nothing Then
Range('C66') = 3
Range('C65') = Empty
Range('F66') = Range('B66')
End If
If Not Intersect(Target, Range('C67')) Is Nothing Then
Range('C67') = 3
Range('C68') = Empty
Range('F67') = Range('B67')
ElseIf Not Intersect(Target, Range('C68')) Is Nothing Then
Range('C68') = 3
Range('C67') = Empty
Range('F67') = Range('B68')
End If
If Not Intersect(Target, Range('C69')) Is Nothing Then
Range('C69') = 3
Range('C70') = Empty
Range('F70') = Range('B69')
ElseIf Not Intersect(Target, Range('C70')) Is Nothing Then
Range('C70') = 3
Range('C69') = Empty
Range('F70') = Range('B70')
End If
If Not Intersect(Target, Range('C71')) Is Nothing Then
Range('C71') = 3
Range('C72') = Empty
Range('F71') = Range('B71')
ElseIf Not Intersect(Target, Range('C72')) Is Nothing Then
Range('C72') = 3
Range('C71') = Empty
Range('F71') = Range('B72')
End If
..........je sèche !!
Vous voyez la solution ??
----------------
Merci d'avance.
Domi