Simplification de code

[anber]

Bonsoir le forum,

Le code suivant peut-il être optimisé ?

For Each cel In Range("D9😀" & Range("D65536").End(xlUp).Row)
cel = RTrim(cel)
If cel = 1 Or cel = "A" Then
If Sheets("Moa").Range("A8") = "" Then
Set DEST = Sheets("Moa").Range("A8")
Else
Set DEST = Sheets("Moa").Range("A65536").End(xlUp).Offset(1, 0)
End If
Range(cel.Offset(0, -1), cel.Offset(0, 10)).Copy Destination:=DEST

ElseIf cel = 2 Or cel = "B" Then
If Sheets("Mov").Range("A8") = "" Then
Set DEST = Sheets("Mov").Range("A8")
Else
Set DEST = Sheets("Mov").Range("A65536").End(xlUp).Offset(1, 0)
End If
Range(cel.Offset(0, -1), cel.Offset(0, 10)).Copy Destination:=DEST

ElseIf cel = 8 Or cel = "C" Then
If Sheets("PP").Range("A8") = "" Then
Set DEST = Sheets("PP").Range("A8")
Else
Set DEST = Sheets("PP").Range("A65536").End(xlUp).Offset(1, 0)
End If
Range(cel.Offset(0, -1), cel.Offset(0, 10)).Copy Destination:=DEST

ElseIf cel = 9 Or cel = "D" Then
If Sheets("Che").Range("A8") = "" Then
Set DEST = Sheets("Che").Range("A8")
Else
Set DEST = Sheets("Che").Range("A65536").End(xlUp).Offset(1, 0)
End If
Range(cel.Offset(0, -1), cel.Offset(0, 10)).Copy Destination:=DEST

ElseIf cel = "A" Or cel = "B" Or cel = "C" Or cel = "D" Then
If Sheets("Pan").Range("A8") = "" Then
Set DEST = Sheets("Pan").Range("A8")
Else
Set DEST = Sheets("Pan").Range("A65536").End(xlUp).Offset(1, 0)
End If
Range(cel.Offset(0, -1), cel.Offset(0, 10)).Copy Destination:=DEST
End If
Next cel

Merci

 

[JMG]

Bonjour

regarde Select Case plutôt que ElseIf

amicalement

JMG

 

[anber]

Salut JMG

Merci pour ta réponse, c'est bien plus simple avec Select Case


Cordialement