Macro "SI" erreur de compilation

Créer une discussion
Répondre

Boostez vos compétences Excel avec notre communauté !

Rejoignez Excel Downloads, le rendez-vous des passionnés où l'entraide fait la force. Apprenez, échangez, progressez – et tout ça gratuitement ! 👉 Inscrivez-vous maintenant !

S'inscrire
Usine à gaz

Usine à gaz

XLDnaute Barbatruc
Supporter XLD
Bonjour à tous,

Je ne m'en sors pas avec tous ces IF et End If
J'ai besoin de votre aide.

Pour la macro si dessous, j'ai le message d'erreur suivant :

"erreur de compilation Bloc If sans End If"

Pourtant, j'en ai mis partout !!! LOL

Voici la macro :

HTML: 
Sub OFnlCal()
'
' OFnlCal Macro
'

'
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Feuil13.Select
    ActiveSheet.Unprotect

    Sheets("SM (2)").Select
    If [k1] = "NL" Then
    Columns("B:L").Select
    Selection.Copy
    Feuil13.Select
    Range("B1").Select
    ActiveSheet.Paste
    Sheets("SM (2)").Select
    Columns("A:L").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    End If
    Sheets("SM (2)").Select
    If [k1] = "NL" Then
    Columns("A:L").Select
    Selection.Copy
    Feuil13.Select
    Range("M1").Select
    ActiveSheet.Paste
    Sheets("SM (2)").Select
    Columns("A:L").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    End If
    Sheets("SM (2)").Select
    If [k1] = "NL" Then
    Columns("A:L").Select
    Selection.Copy
    Feuil13.Select
    Range("Y1").Select
    ActiveSheet.Paste
    Sheets("SM (2)").Select
    Columns("A:L").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    End If
    Sheets("SM (2)").Select
    If [k1] = "NL" Then
    Columns("A:L").Select
    Selection.Copy
    Feuil13.Select
    Range("AK1").Select
    ActiveSheet.Paste
    Sheets("SM (2)").Select
    Columns("A:L").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    End If
    Sheets("SM (2)").Select
    If [k1] = "NL" Then
    Columns("A:L").Select
    Selection.Copy
    Feuil13.Select
    Range("AW1").Select
    ActiveSheet.Paste
    Sheets("SM (2)").Select
    Columns("A:L").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    End If
    Sheets("SM (2)").Select
    If [k1] = "NL" Then
    Columns("A:L").Select
    Selection.Copy
    Feuil13.Select
    Range("BI1").Select
    ActiveSheet.Paste
    Sheets("SM (2)").Select
    Columns("A:L").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    End If
    Sheets("SM (2)").Select
    If [k1] = "NL" Then
    Columns("A:L").Select
    Selection.Copy
    Feuil13.Select
    Range("BU1").Select
    ActiveSheet.Paste
    Sheets("SM (2)").Select
    Columns("A:L").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    End If
    Sheets("SM (2)").Select
    If [k1] = "NL" Then
    Columns("A:L").Select
    Selection.Copy
    Feuil13.Select
    Range("CG1").Select
    ActiveSheet.Paste
    Sheets("SM (2)").Select
    Columns("A:L").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    End If
    Sheets("SM (2)").Select
    If [k1] = "NL" Then
    Columns("A:L").Select
    Selection.Copy
    Feuil13.Select
    Range("CS1").Select
    ActiveSheet.Paste
    Sheets("SM (2)").Select
    Columns("A:L").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    End If
    Sheets("SM (2)").Select
    If [k1] = "NL" Then
    Columns("A:L").Select
    Selection.Copy
    Feuil13.Select
    Range("DE1").Select
    ActiveSheet.Paste
    Sheets("SM (2)").Select
    Columns("A:L").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    End If
    Sheets("SM (2)").Select
    If [k1] = "NL" Then
    Columns("A:L").Select
    Selection.Copy
    Feuil13.Select
    Range("DQ1").Select
    ActiveSheet.Paste
    Sheets("SM (2)").Select
    Columns("A:L").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    End If
    Sheets("SM (2)").Select
    If [k1] = "NL" Then
    Columns("A:L").Select
    Selection.Copy
    Feuil13.Select
    Range("EC1").Select
    ActiveSheet.Paste
    Sheets("SM (2)").Select
    Columns("A:L").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    End If
    Sheets("SM (2)").Select
    If [k1] = "NL" Then
    Columns("A:L").Select
    Selection.Copy
    Feuil13.Select
    Range("EO1").Select
    ActiveSheet.Paste
    Sheets("SM (2)").Select
    Columns("A:L").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    End If
    Sheets("SM (2)").Select
    If [k1] = "NL" Then
    Columns("A:L").Select
    Selection.Copy
    Feuil13.Select
    Range("FA1").Select
    ActiveSheet.Paste
    Sheets("SM (2)").Select
    Columns("A:L").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    End If
    Sheets("SM (2)").Select
    If [k1] = "NL" Then
    Columns("A:L").Select
    Selection.Copy
    Feuil13.Select
    Range("FM1").Select
    ActiveSheet.Paste
    Sheets("SM (2)").Select
    Columns("A:L").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    End If
    Sheets("SM (2)").Select
    If [k1] = "NL" Then
    Columns("A:L").Select
    Selection.Copy
    Feuil13.Select
    Range("FY1").Select
    ActiveSheet.Paste
    Sheets("SM (2)").Select
    Columns("A:L").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    End If
    Sheets("SM (2)").Select
    If [k1] = "NL" Then
    Columns("A:L").Select
    Selection.Copy
    Feuil13.Select
    Range("GL1").Select
    ActiveSheet.Paste
    Sheets("SM (2)").Select
    Columns("A:L").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    End If
    Sheets("SM (2)").Select
    If [k1] = "NL" Then
    Columns("A:L").Select
    Selection.Copy
    Feuil13.Select
    Range("GW1").Select
    ActiveSheet.Paste
    Sheets("SM (2)").Select
    Columns("A:L").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    End If
    Sheets("SM (2)").Select
    If [k1] = "NL" Then
    Columns("A:L").Select
    Selection.Copy
    Feuil13.Select
    Range("HI1").Select
    ActiveSheet.Paste
    Sheets("SM (2)").Select
    Columns("A:L").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft

    Sheets("SM (2)").Select
    If [k1] = "NL" Then
    Columns("A:L").Select
    Selection.Copy
    Feuil13.Select
    Range("HU1").Select
    ActiveSheet.Paste
    Sheets("SM (2)").Select
    Columns("A:L").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    End If
    Sheets("SM (2)").Select
    If [k1] = "NL" Then
    Columns("A:L").Select
    Selection.Copy
    Feuil13.Select
    Range("IG1").Select
    ActiveSheet.Paste
    Sheets("SM (2)").Select
    Columns("A:L").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    End If
    Sheets("SM (2)").Select
    If [k1] = "NL" Then
    Columns("A:L").Select
    Selection.Copy
    Feuil13.Select
    Range("IS1").Select
    ActiveSheet.Paste
    Sheets("SM (2)").Select
    Columns("A:L").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    End If
    Sheets("SM (2)").Select
    If [k1] = "NL" Then
    Columns("A:L").Select
    Selection.Copy
    Feuil13.Select
    Range("JE1").Select
    ActiveSheet.Paste
    Sheets("SM (2)").Select
    Columns("A:L").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    End If
    Sheets("SM (2)").Select
    If [k1] = "NL" Then
    Columns("A:L").Select
    Selection.Copy
    Feuil13.Select
    Range("JQ1").Select
    ActiveSheet.Paste
    Sheets("SM (2)").Select
    Columns("A:L").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    End If
    Sheets("SM (2)").Select
    If [k1] = "NL" Then
    Columns("A:L").Select
    Selection.Copy
    Feuil13.Select
    Range("KC1").Select
    ActiveSheet.Paste
    Sheets("SM (2)").Select
    Columns("A:L").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    End If

    Feuil13.Select
    ActiveWindow.SmallScroll ToRight:=-400
    Range("C1").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(OR('OF1'!R2C2="""",'OF1'!R2C2=""n° OF""),0,IF('OF1'!RC3=""CAL"",'OF1'!R2C2,0))"
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-2]=0,"""",'OF1'!RC3)"
    Range("G1").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-4]=0,"""",CONCATENATE(""Sem"",""  "",'OF1'!R[2]C8))"
    Range("I1").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-6]=0,"""",""Qté livrée : "")"
    Range("K1").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-8]=0,0,IF(LOOKUP(R[1]C[-6],Exp)=""NL"",""NL"",LOOKUP(R[1]C[-6],Exp)))"
    Range("B2").Select
    ActiveCell.FormulaR1C1 = "=IF(R[-1]C[1]=0,"""",""Suivi des Rebuts"")"
    Range("E2").Select
    ActiveCell.FormulaR1C1 = "=IF(R[-1]C[-2]=0,"""",'OF1'!RC5)"
    Range("G2").Select
    ActiveCell.FormulaR1C1 = "=IF(R[-1]C[-4]=0,"""",""Période du"")"
    Range("I2").Select
    ActiveCell.FormulaR1C1 = "=IF(R[-1]C[-6]=0,"""",'OF1'!R[1]C10)"
    Range("J2").Select
    ActiveCell.FormulaR1C1 = "=IF(R[-1]C[-7]=0,"""",""au"")"
    Range("K2").Select
    ActiveCell.FormulaR1C1 = "=IF(R[-1]C[-8]=0,"""",'OF1'!R[1]C12)"
    Range("L2").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-1]<>"""",RC[-1],"""")"
    Range("C3").Select
    ActiveCell.FormulaR1C1 = "=IF(R[-2]C=0,"""",""Qte/Tps.Un"")"
    Range("D3").Select
    ActiveCell.FormulaR1C1 = "=IF(R[-2]C[-1]=0,"""",'OF1'!RC4)"
    Range("G3").Select
    ActiveCell.FormulaR1C1 = "=IF(R[-2]C[-4]=0,"""",""Mon tage"")"
    Range("H3").Select
    ActiveCell.FormulaR1C1 = "=IF(R[-2]C[-5]=0,"""",""Client"")"
    Range("I3").Select
    ActiveCell.FormulaR1C1 = "=IF(R[-2]C[-6]=0,"""",""Tampo"")"
    Range("J3").Select
    ActiveCell.FormulaR1C1 = "=IF(R[-2]C[-7]=0,"""",""total Rebuts"")"
    Range("K3").Select
    ActiveCell.FormulaR1C1 = "=IF(R[-2]C[-8]=0,"""",""Qté Util."")"
    Range("L3").Select
    ActiveCell.FormulaR1C1 = "=IF(R[-2]C[-9]=0,"""",""%    rebuts"")"
    Range("C4").Select
    ActiveCell.FormulaR1C1 = "=IF(R1C3=0,0,ROUND('OF1'!R[1]C3,3))"
    Range("D4").Select
    ActiveCell.FormulaR1C1 = "=IF(R1C3=0,0,'OF1'!R[1]C4)"
    Range("E4").Select
    ActiveCell.FormulaR1C1 = "=IF(R1C3=0,0,'OF1'!R[1]C5)"
    Range("F4").Select
    ActiveCell.FormulaR1C1 = "=IF(R1C3=0,0,'OF1'!R[1]C6)"
    Range("C4:F4").Select
    Selection.Copy
    Range("C5:F43").Select
    ActiveSheet.Paste
    Range("J4").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(AND(RC[-6]=0,SUM(RC[-3]:RC[-1])>0),""Ha Ha ?"",IF(R1C[1]=""NL"",SUM(RC[-3]:RC[-1]),IF(SUM(RC[-3]:RC[-1])>(R1C[1]*RC[-7]),""   Trop"",SUM(RC[-3]:RC[-1]))))"
    Range("K4").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-1]="" Ha Ha ?"",0,IF(RC[-1]=0,0,IF(R1C=""NL"",""NL"",IF(R1C>0,R1C*RC[-8],0))))"
    Range("L4").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-2]=""   Trop"",0.00001,IF(AND(RC[-2]=0,RC[-1]=""NL""),0,IF(AND(RC[-2]>0,RC[-1]=""NL""),0.00001,IF(RC[-1]>0,RC[-2]/RC[-1]*100,0))))"
    Range("J4:L4").Select
    Selection.Copy
    Range("J5:L43").Select
    ActiveSheet.Paste
    Range("G4:I43").Select
    Selection.Locked = False
    Selection.FormulaHidden = False
    Range("G4").Select
    Application.CutCopyMode = False
    
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    
    Application.EnableEvents = True
End Sub

Merci de votre aide,
Amicalement,
Lionel,
 
Trier par date Trier par votes
Re : Macro "SI" erreur de compilation

Bonsoir Lionel,

Si tu prends l'habitude d'incrémenter tes lignes de code, il te serait beaucoup plus facile de détecter les erreurs.

En l'occurrence, tu as une condition identique qui figure deux fois, mais la première n'a pas de End If. Je t'ai mis ces lignes en commentaires.

Code: 
Sub OFnlCal()
'
' OFnlCal Macro
'

'
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Feuil13.Select
    ActiveSheet.Unprotect

    Sheets("SM (2)").Select
    If [k1] = "NL" Then
      Columns("B:L").Select
      Selection.Copy
      Feuil13.Select
      Range("B1").Select
      ActiveSheet.Paste
      Sheets("SM (2)").Select
      Columns("A:L").Select
      Application.CutCopyMode = False
      Selection.Delete Shift:=xlToLeft
    End If
    Sheets("SM (2)").Select
    If [k1] = "NL" Then
      Columns("A:L").Select
      Selection.Copy
      Feuil13.Select
      Range("M1").Select
      ActiveSheet.Paste
      Sheets("SM (2)").Select
     Columns("A:L").Select
      Application.CutCopyMode = False
      Selection.Delete Shift:=xlToLeft
    End If
    Sheets("SM (2)").Select
    If [k1] = "NL" Then
      Columns("A:L").Select
      Selection.Copy
      Feuil13.Select
      Range("Y1").Select
      ActiveSheet.Paste
      Sheets("SM (2)").Select
      Columns("A:L").Select
      Application.CutCopyMode = False
      Selection.Delete Shift:=xlToLeft
    End If
    Sheets("SM (2)").Select
    If [k1] = "NL" Then
      Columns("A:L").Select
      Selection.Copy
      Feuil13.Select
      Range("AK1").Select
      ActiveSheet.Paste
      Sheets("SM (2)").Select
      Columns("A:L").Select
      Application.CutCopyMode = False
      Selection.Delete Shift:=xlToLeft
    End If
    Sheets("SM (2)").Select
    If [k1] = "NL" Then
      Columns("A:L").Select
      Selection.Copy
      Feuil13.Select
      Range("AW1").Select
      ActiveSheet.Paste
      Sheets("SM (2)").Select
      Columns("A:L").Select
      Application.CutCopyMode = False
      Selection.Delete Shift:=xlToLeft
    End If
    Sheets("SM (2)").Select
    If [k1] = "NL" Then
      Columns("A:L").Select
      Selection.Copy
      Feuil13.Select
      Range("BI1").Select
      ActiveSheet.Paste
      Sheets("SM (2)").Select
      Columns("A:L").Select
      Application.CutCopyMode = False
      Selection.Delete Shift:=xlToLeft
    End If
    Sheets("SM (2)").Select
    If [k1] = "NL" Then
      Columns("A:L").Select
      Selection.Copy
      Feuil13.Select
      Range("BU1").Select
      ActiveSheet.Paste
      Sheets("SM (2)").Select
      Columns("A:L").Select
      Application.CutCopyMode = False
      Selection.Delete Shift:=xlToLeft
    End If
    Sheets("SM (2)").Select
    If [k1] = "NL" Then
      Columns("A:L").Select
      Selection.Copy
      Feuil13.Select
      Range("CG1").Select
      ActiveSheet.Paste
      Sheets("SM (2)").Select
      Columns("A:L").Select
      Application.CutCopyMode = False
      Selection.Delete Shift:=xlToLeft
    End If
    Sheets("SM (2)").Select
    If [k1] = "NL" Then
      Columns("A:L").Select
      Selection.Copy
      Feuil13.Select
      Range("CS1").Select
      ActiveSheet.Paste
      Sheets("SM (2)").Select
      Columns("A:L").Select
      Application.CutCopyMode = False
      Selection.Delete Shift:=xlToLeft
    End If
    Sheets("SM (2)").Select
    If [k1] = "NL" Then
      Columns("A:L").Select
      Selection.Copy
      Feuil13.Select
      Range("DE1").Select
      ActiveSheet.Paste
      Sheets("SM (2)").Select
      Columns("A:L").Select
      Application.CutCopyMode = False
      Selection.Delete Shift:=xlToLeft
    End If
    Sheets("SM (2)").Select
    If [k1] = "NL" Then
      Columns("A:L").Select
      Selection.Copy
      Feuil13.Select
      Range("DQ1").Select
      ActiveSheet.Paste
      Sheets("SM (2)").Select
      Columns("A:L").Select
      Application.CutCopyMode = False
      Selection.Delete Shift:=xlToLeft
    End If
    Sheets("SM (2)").Select
    If [k1] = "NL" Then
      Columns("A:L").Select
      Selection.Copy
      Feuil13.Select
      Range("EC1").Select
      ActiveSheet.Paste
      Sheets("SM (2)").Select
      Columns("A:L").Select
      Application.CutCopyMode = False
      Selection.Delete Shift:=xlToLeft
    End If
    Sheets("SM (2)").Select
    If [k1] = "NL" Then
      Columns("A:L").Select
      Selection.Copy
      Feuil13.Select
      Range("EO1").Select
      ActiveSheet.Paste
      Sheets("SM (2)").Select
      Columns("A:L").Select
      Application.CutCopyMode = False
      Selection.Delete Shift:=xlToLeft
    End If
    Sheets("SM (2)").Select
    If [k1] = "NL" Then
      Columns("A:L").Select
      Selection.Copy
      Feuil13.Select
      Range("FA1").Select
      ActiveSheet.Paste
      Sheets("SM (2)").Select
      Columns("A:L").Select
      Application.CutCopyMode = False
      Selection.Delete Shift:=xlToLeft
    End If
    Sheets("SM (2)").Select
    If [k1] = "NL" Then
      Columns("A:L").Select
      Selection.Copy
      Feuil13.Select
      Range("FM1").Select
      ActiveSheet.Paste
      Sheets("SM (2)").Select
      Columns("A:L").Select
      Application.CutCopyMode = False
      Selection.Delete Shift:=xlToLeft
    End If
    Sheets("SM (2)").Select
    If [k1] = "NL" Then
      Columns("A:L").Select
      Selection.Copy
      Feuil13.Select
      Range("FY1").Select
      ActiveSheet.Paste
      Sheets("SM (2)").Select
      Columns("A:L").Select
      Application.CutCopyMode = False
      Selection.Delete Shift:=xlToLeft
    End If
    Sheets("SM (2)").Select
    If [k1] = "NL" Then
      Columns("A:L").Select
      Selection.Copy
      Feuil13.Select
      Range("GL1").Select
      ActiveSheet.Paste
      Sheets("SM (2)").Select
      Columns("A:L").Select
      Application.CutCopyMode = False
      Selection.Delete Shift:=xlToLeft
    End If
    Sheets("SM (2)").Select
    If [k1] = "NL" Then
      Columns("A:L").Select
      Selection.Copy
      Feuil13.Select
      Range("GW1").Select
      ActiveSheet.Paste
      Sheets("SM (2)").Select
      Columns("A:L").Select
      Application.CutCopyMode = False
      Selection.Delete Shift:=xlToLeft
    End If
    Sheets("SM (2)").Select
    If [k1] = "NL" Then
      Columns("A:L").Select
      Selection.Copy
      Feuil13.Select
      Range("HI1").Select
      ActiveSheet.Paste
      Sheets("SM (2)").Select
      Columns("A:L").Select
      Application.CutCopyMode = False
      Selection.Delete Shift:=xlToLeft
      Sheets("SM (2)").Select
'    If [k1] = "NL" Then
'    Columns("A:L").Select
'    Selection.Copy
'    Feuil13.Select
'    Range("HU1").Select
'    ActiveSheet.Paste
'    Sheets("SM (2)").Select
'    Columns("A:L").Select
'    Application.CutCopyMode = False
'    Selection.Delete Shift:=xlToLeft
    End If
    Sheets("SM (2)").Select
    If [k1] = "NL" Then
      Columns("A:L").Select
      Selection.Copy
      Feuil13.Select
      Range("IG1").Select
      ActiveSheet.Paste
      Sheets("SM (2)").Select
      Columns("A:L").Select
      Application.CutCopyMode = False
      Selection.Delete Shift:=xlToLeft
    End If
    Sheets("SM (2)").Select
    If [k1] = "NL" Then
      Columns("A:L").Select
      Selection.Copy
      Feuil13.Select
      Range("IS1").Select
      ActiveSheet.Paste
      Sheets("SM (2)").Select
      Columns("A:L").Select
      Application.CutCopyMode = False
      Selection.Delete Shift:=xlToLeft
    End If
    Sheets("SM (2)").Select
    If [k1] = "NL" Then
      Columns("A:L").Select
      Selection.Copy
      Feuil13.Select
      Range("JE1").Select
      ActiveSheet.Paste
      Sheets("SM (2)").Select
      Columns("A:L").Select
      Application.CutCopyMode = False
      Selection.Delete Shift:=xlToLeft
    End If
    Sheets("SM (2)").Select
    If [k1] = "NL" Then
      Columns("A:L").Select
      Selection.Copy
      Feuil13.Select
      Range("JQ1").Select
      ActiveSheet.Paste
      Sheets("SM (2)").Select
      Columns("A:L").Select
      Application.CutCopyMode = False
      Selection.Delete Shift:=xlToLeft
    End If
    Sheets("SM (2)").Select
    If [k1] = "NL" Then
      Columns("A:L").Select
      Selection.Copy
      Feuil13.Select
      Range("KC1").Select
      ActiveSheet.Paste
      Sheets("SM (2)").Select
      Columns("A:L").Select
     Application.CutCopyMode = False
      Selection.Delete Shift:=xlToLeft
    End If

    Feuil13.Select
    ActiveWindow.SmallScroll ToRight:=-400
    Range("C1").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(OR('OF1'!R2C2="""",'OF1'!R2C2=""n° OF""),0,IF('OF1'!RC3=""CAL"",'OF1'!R2C2,0))"
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-2]=0,"""",'OF1'!RC3)"
    Range("G1").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-4]=0,"""",CONCATENATE(""Sem"",""  "",'OF1'!R[2]C8))"
    Range("I1").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-6]=0,"""",""Qté livrée : "")"
    Range("K1").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-8]=0,0,IF(LOOKUP(R[1]C[-6],Exp)=""NL"",""NL"",LOOKUP(R[1]C[-6],Exp)))"
    Range("B2").Select
    ActiveCell.FormulaR1C1 = "=IF(R[-1]C[1]=0,"""",""Suivi des Rebuts"")"
    Range("E2").Select
    ActiveCell.FormulaR1C1 = "=IF(R[-1]C[-2]=0,"""",'OF1'!RC5)"
    Range("G2").Select
    ActiveCell.FormulaR1C1 = "=IF(R[-1]C[-4]=0,"""",""Période du"")"
    Range("I2").Select
    ActiveCell.FormulaR1C1 = "=IF(R[-1]C[-6]=0,"""",'OF1'!R[1]C10)"
    Range("J2").Select
    ActiveCell.FormulaR1C1 = "=IF(R[-1]C[-7]=0,"""",""au"")"
    Range("K2").Select
    ActiveCell.FormulaR1C1 = "=IF(R[-1]C[-8]=0,"""",'OF1'!R[1]C12)"
    Range("L2").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-1]<>"""",RC[-1],"""")"
    Range("C3").Select
    ActiveCell.FormulaR1C1 = "=IF(R[-2]C=0,"""",""Qte/Tps.Un"")"
    Range("D3").Select
    ActiveCell.FormulaR1C1 = "=IF(R[-2]C[-1]=0,"""",'OF1'!RC4)"
    Range("G3").Select
    ActiveCell.FormulaR1C1 = "=IF(R[-2]C[-4]=0,"""",""Mon tage"")"
    Range("H3").Select
    ActiveCell.FormulaR1C1 = "=IF(R[-2]C[-5]=0,"""",""Client"")"
    Range("I3").Select
    ActiveCell.FormulaR1C1 = "=IF(R[-2]C[-6]=0,"""",""Tampo"")"
    Range("J3").Select
    ActiveCell.FormulaR1C1 = "=IF(R[-2]C[-7]=0,"""",""total Rebuts"")"
    Range("K3").Select
    ActiveCell.FormulaR1C1 = "=IF(R[-2]C[-8]=0,"""",""Qté Util."")"
    Range("L3").Select
    ActiveCell.FormulaR1C1 = "=IF(R[-2]C[-9]=0,"""",""%    rebuts"")"
    Range("C4").Select
    ActiveCell.FormulaR1C1 = "=IF(R1C3=0,0,ROUND('OF1'!R[1]C3,3))"
    Range("D4").Select
    ActiveCell.FormulaR1C1 = "=IF(R1C3=0,0,'OF1'!R[1]C4)"
    Range("E4").Select
    ActiveCell.FormulaR1C1 = "=IF(R1C3=0,0,'OF1'!R[1]C5)"
    Range("F4").Select
    ActiveCell.FormulaR1C1 = "=IF(R1C3=0,0,'OF1'!R[1]C6)"
    Range("C4:F4").Select
    Selection.Copy
    Range("C5:F43").Select
    ActiveSheet.Paste
    Range("J4").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(AND(RC[-6]=0,SUM(RC[-3]:RC[-1])>0),""Ha Ha ?"",IF(R1C[1]=""NL"",SUM(RC[-3]:RC[-1]),IF(SUM(RC[-3]:RC[-1])>(R1C[1]*RC[-7]),""   Trop"",SUM(RC[-3]:RC[-1]))))"
    Range("K4").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-1]="" Ha Ha ?"",0,IF(RC[-1]=0,0,IF(R1C=""NL"",""NL"",IF(R1C>0,R1C*RC[-8],0))))"
    Range("L4").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-2]=""   Trop"",0.00001,IF(AND(RC[-2]=0,RC[-1]=""NL""),0,IF(AND(RC[-2]>0,RC[-1]=""NL""),0.00001,IF(RC[-1]>0,RC[-2]/RC[-1]*100,0))))"
    Range("J4:L4").Select
    Selection.Copy
    Range("J5:L43").Select
    ActiveSheet.Paste
    Range("G4:I43").Select
    Selection.Locked = False
    Selection.FormulaHidden = False
    Range("G4").Select
    Application.CutCopyMode = False
    
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    
    Application.EnableEvents = True
End Sub
Cordialement.
 
Vote positif 0 Vote négatif
Re : Macro "SI" erreur de compilation

RE :

Après mûre réflexion, je te propose un condensé de ton code: j'y ai éliminé moult lignes qui sont inutilement répétitives.

Je n'ai pu vérifier s'il fonctionne correctement puisque je n'ai pas le fichier sur mon poste, mais je pense que ça devrait le faire.

Code: 
Sub OFnlCal()
'
' OFnlCal Macro
'

'
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Feuil13.Unprotect
    
  Sheets("SM (2)").Select
    If [k1] = "NL" Then
      Columns("B:L").Copy Destination:=Feuil13.Range("B1")
      Columns("A:L").Delete Shift:=xlToLeft
      Columns("A:L").Copy Destination:=Feuil13.Range("M1")
      Columns("A:L").Delete Shift:=xlToLeft
      Columns("A:L").Copy Destination:=Feuil13.Range("Y1")
      Columns("A:L").Delete Shift:=xlToLeft
      Columns("A:L").Copy Destination:=Feuil13.Range("AK1")
      Columns("A:L").Delete Shift:=xlToLeft
      Columns("A:L").Copy Destination:=Feuil13.Range("AW1")
      Columns("A:L").Delete Shift:=xlToLeft
      Columns("A:L").Copy Destination:=Feuil13.Range("BI1")
      Columns("A:L").Delete Shift:=xlToLeft
      Columns("A:L").Copy Destination:=Feuil13.Range("BU1")
      Columns("A:L").Delete Shift:=xlToLeft
      Columns("A:L").Copy Destination:=Feuil13.Range("CG1")
      Columns("A:L").Delete Shift:=xlToLeft
      Columns("A:L").Copy Destination:=Feuil13.Range("CS1")
      Columns("A:L").Delete Shift:=xlToLeft
      Columns("A:L").Copy Destination:=Feuil13.Range("DE1")
      Columns("A:L").Delete Shift:=xlToLeft
      Columns("A:L").Copy Destination:=Feuil13.Range("DQ1")
      Columns("A:L").Delete Shift:=xlToLeft
      Columns("A:L").Copy Destination:=Feuil13.Range("EC1")
      Columns("A:L").Delete Shift:=xlToLeft
      Columns("A:L").Copy Destination:=Feuil13.Range("EO1")
      Columns("A:L").Delete Shift:=xlToLeft
      Columns("A:L").Copy Destination:=Feuil13.Range("FA1")
      Columns("A:L").Delete Shift:=xlToLeft
      Columns("A:L").Copy Destination:=Feuil13.Range("FM1")
      Columns("A:L").Delete Shift:=xlToLeft
      Columns("A:L").Copy Destination:=Feuil13.Range("FY1")
      Columns("A:L").Delete Shift:=xlToLeft
      Columns("A:L").Copy Destination:=Feuil13.Range("GL1")
      Columns("A:L").Delete Shift:=xlToLeft
      Columns("A:L").Copy Destination:=Feuil13.Range("GW1")
      Columns("A:L").Delete Shift:=xlToLeft
      Columns("A:L").Copy Destination:=Feuil13.Range("HI1")
      Columns("A:L").Delete Shift:=xlToLeft
      Columns("A:L").Copy Destination:=Feuil13.Range("HU1")
      Columns("A:L").Delete Shift:=xlToLeft
      Columns("A:L").Copy Destination:=Feuil13.Range("IG1")
      Columns("A:L").Delete Shift:=xlToLeft
      Columns("A:L").Copy Destination:=Feuil13.Range("IS1")
      Columns("A:L").Delete Shift:=xlToLeft
      Columns("A:L").Copy Destination:=Feuil13.Range("JE1")
      Columns("A:L").Delete Shift:=xlToLeft
      Columns("A:L").Copy Destination:=Feuil13.Range("JQ1")
      Columns("A:L").Delete Shift:=xlToLeft
      Columns("A:L").Copy Destination:=Feuil13.Range("KC1")
      Columns("A:L").Delete Shift:=xlToLeft
      Application.CutCopyMode = False
    End If

    Feuil13.Select
    ActiveWindow.SmallScroll ToRight:=-400
    Range("C1").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(OR('OF1'!R2C2="""",'OF1'!R2C2=""n° OF""),0,IF('OF1'!RC3=""CAL"",'OF1'!R2C2,0))"
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-2]=0,"""",'OF1'!RC3)"
    Range("G1").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-4]=0,"""",CONCATENATE(""Sem"",""  "",'OF1'!R[2]C8))"
    Range("I1").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-6]=0,"""",""Qté livrée : "")"
    Range("K1").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-8]=0,0,IF(LOOKUP(R[1]C[-6],Exp)=""NL"",""NL"",LOOKUP(R[1]C[-6],Exp)))"
    Range("B2").Select
    ActiveCell.FormulaR1C1 = "=IF(R[-1]C[1]=0,"""",""Suivi des Rebuts"")"
    Range("E2").Select
    ActiveCell.FormulaR1C1 = "=IF(R[-1]C[-2]=0,"""",'OF1'!RC5)"
    Range("G2").Select
    ActiveCell.FormulaR1C1 = "=IF(R[-1]C[-4]=0,"""",""Période du"")"
    Range("I2").Select
    ActiveCell.FormulaR1C1 = "=IF(R[-1]C[-6]=0,"""",'OF1'!R[1]C10)"
    Range("J2").Select
    ActiveCell.FormulaR1C1 = "=IF(R[-1]C[-7]=0,"""",""au"")"
    Range("K2").Select
    ActiveCell.FormulaR1C1 = "=IF(R[-1]C[-8]=0,"""",'OF1'!R[1]C12)"
    Range("L2").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-1]<>"""",RC[-1],"""")"
    Range("C3").Select
    ActiveCell.FormulaR1C1 = "=IF(R[-2]C=0,"""",""Qte/Tps.Un"")"
    Range("D3").Select
    ActiveCell.FormulaR1C1 = "=IF(R[-2]C[-1]=0,"""",'OF1'!RC4)"
    Range("G3").Select
    ActiveCell.FormulaR1C1 = "=IF(R[-2]C[-4]=0,"""",""Mon tage"")"
    Range("H3").Select
    ActiveCell.FormulaR1C1 = "=IF(R[-2]C[-5]=0,"""",""Client"")"
    Range("I3").Select
    ActiveCell.FormulaR1C1 = "=IF(R[-2]C[-6]=0,"""",""Tampo"")"
    Range("J3").Select
    ActiveCell.FormulaR1C1 = "=IF(R[-2]C[-7]=0,"""",""total Rebuts"")"
    Range("K3").Select
    ActiveCell.FormulaR1C1 = "=IF(R[-2]C[-8]=0,"""",""Qté Util."")"
    Range("L3").Select
    ActiveCell.FormulaR1C1 = "=IF(R[-2]C[-9]=0,"""",""%    rebuts"")"
    Range("C4").Select
    ActiveCell.FormulaR1C1 = "=IF(R1C3=0,0,ROUND('OF1'!R[1]C3,3))"
    Range("D4").Select
    ActiveCell.FormulaR1C1 = "=IF(R1C3=0,0,'OF1'!R[1]C4)"
    Range("E4").Select
    ActiveCell.FormulaR1C1 = "=IF(R1C3=0,0,'OF1'!R[1]C5)"
    Range("F4").Select
    ActiveCell.FormulaR1C1 = "=IF(R1C3=0,0,'OF1'!R[1]C6)"
    Range("C4:F4").Select
    Selection.Copy
    Range("C5:F43").Select
    ActiveSheet.Paste
    Range("J4").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(AND(RC[-6]=0,SUM(RC[-3]:RC[-1])>0),""Ha Ha ?"",IF(R1C[1]=""NL"",SUM(RC[-3]:RC[-1]),IF(SUM(RC[-3]:RC[-1])>(R1C[1]*RC[-7]),""   Trop"",SUM(RC[-3]:RC[-1]))))"
    Range("K4").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-1]="" Ha Ha ?"",0,IF(RC[-1]=0,0,IF(R1C=""NL"",""NL"",IF(R1C>0,R1C*RC[-8],0))))"
    Range("L4").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-2]=""   Trop"",0.00001,IF(AND(RC[-2]=0,RC[-1]=""NL""),0,IF(AND(RC[-2]>0,RC[-1]=""NL""),0.00001,IF(RC[-1]>0,RC[-2]/RC[-1]*100,0))))"
    Range("J4:L4").Select
    Selection.Copy
    Range("J5:L43").Select
    ActiveSheet.Paste
    Range("G4:I43").Select
    Selection.Locked = False
    Selection.FormulaHidden = False
    Range("G4").Select
    Application.CutCopyMode = False
    
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    Application.EnableEvents = True
End Sub
A +

Cordialement.
 
Vote positif 0 Vote négatif
Re : Macro "SI" erreur de compilation

RE :

En continuant dans la foulée, et pour le fun, une version encore grandement raccourcie. C'est une boucle qui se charge de recopier les données dans Feuil13 :

Code: 
Sub OFnlCal()
'
' OFnlCal Macro
'

'
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Feuil13.Unprotect
    
  Sheets("SM (2)").Select
    If [k1] = "NL" Then
      Columns("B:L").Copy Destination:=Feuil13.Range("B1")
      Columns("A:L").Delete Shift:=xlToLeft
      For col = 12 To 289 Step 12
        Columns("A:L").Copy Destination:=Feuil13.Range("A1").Offset(0, col)
        Columns("A:L").Delete Shift:=xlToLeft
      Next
      Application.CutCopyMode = False
    End If

    Feuil13.Select
    ActiveWindow.SmallScroll ToRight:=-400
    Range("C1").FormulaR1C1 = _
        "=IF(OR('OF1'!R2C2="""",'OF1'!R2C2=""n° OF""),0,IF('OF1'!RC3=""CAL"",'OF1'!R2C2,0))"
    Range("E1").FormulaR1C1 = "=IF(RC[-2]=0,"""",'OF1'!RC3)"
    Range("G1").FormulaR1C1 = _
        "=IF(RC[-4]=0,"""",CONCATENATE(""Sem"",""  "",'OF1'!R[2]C8))"
    Range("I1").FormulaR1C1 = "=IF(RC[-6]=0,"""",""Qté livrée : "")"
    Range("K1").FormulaR1C1 = _
        "=IF(RC[-8]=0,0,IF(LOOKUP(R[1]C[-6],Exp)=""NL"",""NL"",LOOKUP(R[1]C[-6],Exp)))"
    Range("B2").FormulaR1C1 = "=IF(R[-1]C[1]=0,"""",""Suivi des Rebuts"")"
    Range("E2").FormulaR1C1 = "=IF(R[-1]C[-2]=0,"""",'OF1'!RC5)"
    Range("G2").FormulaR1C1 = "=IF(R[-1]C[-4]=0,"""",""Période du"")"
    Range("I2").FormulaR1C1 = "=IF(R[-1]C[-6]=0,"""",'OF1'!R[1]C10)"
    Range("J2").FormulaR1C1 = "=IF(R[-1]C[-7]=0,"""",""au"")"
    Range("K2").FormulaR1C1 = "=IF(R[-1]C[-8]=0,"""",'OF1'!R[1]C12)"
    Range("L2").FormulaR1C1 = "=IF(RC[-1]<>"""",RC[-1],"""")"
    Range("C3").FormulaR1C1 = "=IF(R[-2]C=0,"""",""Qte/Tps.Un"")"
    Range("D3").FormulaR1C1 = "=IF(R[-2]C[-1]=0,"""",'OF1'!RC4)"
    Range("G3").FormulaR1C1 = "=IF(R[-2]C[-4]=0,"""",""Mon tage"")"
    Range("H3").FormulaR1C1 = "=IF(R[-2]C[-5]=0,"""",""Client"")"
    Range("I3").FormulaR1C1 = "=IF(R[-2]C[-6]=0,"""",""Tampo"")"
    Range("J3").FormulaR1C1 = "=IF(R[-2]C[-7]=0,"""",""total Rebuts"")"
    Range("K3").FormulaR1C1 = "=IF(R[-2]C[-8]=0,"""",""Qté Util."")"
    Range("L3").FormulaR1C1 = "=IF(R[-2]C[-9]=0,"""",""%    rebuts"")"
    Range("C4").FormulaR1C1 = "=IF(R1C3=0,0,ROUND('OF1'!R[1]C3,3))"
    Range("D4").FormulaR1C1 = "=IF(R1C3=0,0,'OF1'!R[1]C4)"
    Range("E4").FormulaR1C1 = "=IF(R1C3=0,0,'OF1'!R[1]C5)"
    Range("F4").FormulaR1C1 = "=IF(R1C3=0,0,'OF1'!R[1]C6)"
    Range("C4:F4").Copy
    Range("C5:F43").Paste
    Range("J4").FormulaR1C1 = _
        "=IF(AND(RC[-6]=0,SUM(RC[-3]:RC[-1])>0),""Ha Ha ?"",IF(R1C[1]=""NL"",SUM(RC[-3]:RC[-1]),IF(SUM(RC[-3]:RC[-1])>(R1C[1]*RC[-7]),""   Trop"",SUM(RC[-3]:RC[-1]))))"
    Range("K4").FormulaR1C1 = _
        "=IF(RC[-1]="" Ha Ha ?"",0,IF(RC[-1]=0,0,IF(R1C=""NL"",""NL"",IF(R1C>0,R1C*RC[-8],0))))"
    Range("L4").FormulaR1C1 = _
        "=IF(RC[-2]=""   Trop"",0.00001,IF(AND(RC[-2]=0,RC[-1]=""NL""),0,IF(AND(RC[-2]>0,RC[-1]=""NL""),0.00001,IF(RC[-1]>0,RC[-2]/RC[-1]*100,0))))"
    Range("J4:L4").Copy
    Range("J5:L43").Paste
    Range("G4:I43").Locked = False
    Selection.FormulaHidden = False
    Range("G4").Select
    Application.CutCopyMode = False
    
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    Application.EnableEvents = True
End Sub
Par ailleurs, un petit rappel utile : il n'est pratiquement jamais nécessaire de sélectionner des cellules (ni même des feuilles) pour y écrire, il suffit de les mentionner dans l'instruction.

Toujours pas testé mais si tu as des soucis, tu peux toujours joindre ton classeur.

Cordialement.
 
Vote positif 0 Vote négatif
Re : Macro "SI" erreur de compilation

Oups, je viens de m'apercevoir, dans le #2, que la condition n'est pas en double mais qu'il manque simplement le End If.

Par contre, quand tu auras testé les dernières propositions, fais-nous savoir si ça marche stp.

Amicalement.
 
Vote positif 0 Vote négatif
Re : Macro "SI" erreur de compilation

Petite erreur d'index dans la boucle du message #5.

Voici la correction :

Code: 
Sub OFnlCal()
'
' OFnlCal Macro
'

'
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Feuil13.Unprotect
    
  Sheets("SM (2)").Select
    If [k1] = "NL" Then
      Columns("B:L").Copy Destination:=Feuil13.Range("B1")
      Columns("A:L").Delete Shift:=xlToLeft
      For col = 13 To 289 Step 12
        Columns("A:L").Copy Destination:=Feuil13.Range("A1").Offset(0, col)
        Columns("A:L").Delete Shift:=xlToLeft
      Next
      Application.CutCopyMode = False
    End If

    Feuil13.Select
    ActiveWindow.SmallScroll ToRight:=-400
    Range("C1").FormulaR1C1 = _
        "=IF(OR('OF1'!R2C2="""",'OF1'!R2C2=""n° OF""),0,IF('OF1'!RC3=""CAL"",'OF1'!R2C2,0))"
    Range("E1").FormulaR1C1 = "=IF(RC[-2]=0,"""",'OF1'!RC3)"
    Range("G1").FormulaR1C1 = _
        "=IF(RC[-4]=0,"""",CONCATENATE(""Sem"",""  "",'OF1'!R[2]C8))"
    Range("I1").FormulaR1C1 = "=IF(RC[-6]=0,"""",""Qté livrée : "")"
    Range("K1").FormulaR1C1 = _
        "=IF(RC[-8]=0,0,IF(LOOKUP(R[1]C[-6],Exp)=""NL"",""NL"",LOOKUP(R[1]C[-6],Exp)))"
    Range("B2").FormulaR1C1 = "=IF(R[-1]C[1]=0,"""",""Suivi des Rebuts"")"
    Range("E2").FormulaR1C1 = "=IF(R[-1]C[-2]=0,"""",'OF1'!RC5)"
    Range("G2").FormulaR1C1 = "=IF(R[-1]C[-4]=0,"""",""Période du"")"
    Range("I2").FormulaR1C1 = "=IF(R[-1]C[-6]=0,"""",'OF1'!R[1]C10)"
    Range("J2").FormulaR1C1 = "=IF(R[-1]C[-7]=0,"""",""au"")"
    Range("K2").FormulaR1C1 = "=IF(R[-1]C[-8]=0,"""",'OF1'!R[1]C12)"
    Range("L2").FormulaR1C1 = "=IF(RC[-1]<>"""",RC[-1],"""")"
    Range("C3").FormulaR1C1 = "=IF(R[-2]C=0,"""",""Qte/Tps.Un"")"
    Range("D3").FormulaR1C1 = "=IF(R[-2]C[-1]=0,"""",'OF1'!RC4)"
    Range("G3").FormulaR1C1 = "=IF(R[-2]C[-4]=0,"""",""Mon tage"")"
    Range("H3").FormulaR1C1 = "=IF(R[-2]C[-5]=0,"""",""Client"")"
    Range("I3").FormulaR1C1 = "=IF(R[-2]C[-6]=0,"""",""Tampo"")"
    Range("J3").FormulaR1C1 = "=IF(R[-2]C[-7]=0,"""",""total Rebuts"")"
    Range("K3").FormulaR1C1 = "=IF(R[-2]C[-8]=0,"""",""Qté Util."")"
    Range("L3").FormulaR1C1 = "=IF(R[-2]C[-9]=0,"""",""%    rebuts"")"
    Range("C4").FormulaR1C1 = "=IF(R1C3=0,0,ROUND('OF1'!R[1]C3,3))"
    Range("D4").FormulaR1C1 = "=IF(R1C3=0,0,'OF1'!R[1]C4)"
    Range("E4").FormulaR1C1 = "=IF(R1C3=0,0,'OF1'!R[1]C5)"
    Range("F4").FormulaR1C1 = "=IF(R1C3=0,0,'OF1'!R[1]C6)"
    Range("C4:F4").Copy
    Range("C5:F43").Paste
    Range("J4").FormulaR1C1 = _
        "=IF(AND(RC[-6]=0,SUM(RC[-3]:RC[-1])>0),""Ha Ha  ?"",IF(R1C[1]=""NL"",SUM(RC[-3]:RC[-1]),IF(SUM(RC[-3]:RC[-1])>(R1C[1]*RC[-7]),""    Trop"",SUM(RC[-3]:RC[-1]))))"
    Range("K4").FormulaR1C1 = _
        "=IF(RC[-1]="" Ha Ha ?"",0,IF(RC[-1]=0,0,IF(R1C=""NL"",""NL"",IF(R1C>0,R1C*RC[-8],0))))"
    Range("L4").FormulaR1C1 = _
        "=IF(RC[-2]=""    Trop"",0.00001,IF(AND(RC[-2]=0,RC[-1]=""NL""),0,IF(AND(RC[-2]>0,RC[-1]=""NL""),0.00001,IF(RC[-1]>0,RC[-2]/RC[-1]*100,0))))"
    Range("J4:L4").Copy
    Range("J5:L43").Paste
    Range("G4:I43").Locked = False
    Selection.FormulaHidden = False
    Range("G4").Select
    Application.CutCopyMode = False
    
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    Application.EnableEvents = True
End Sub
Amicalement.
 
Vote positif 0 Vote négatif
Re : Macro "SI" erreur de compilation

Vraiment merci de donner autant d'aide.
Pour ce soir, je suis "crevé" de ma journée et je ne vais pas tarder à aller au repos.

Je tente, avant de l'essayer, de comprendre ton code ...
je poursuivrai demain.
Bonne fin de soirée à toi et à tous,
Amicalement,
Lionel,
 
Vote positif 0 Vote négatif
Re : Macro "SI" erreur de compilation

Bonjour Papou-net,
Bonjour à tous,

Décidément, je ne m'en sors pas de mon problème de suivi.

En plus, j'avais tout faux, faute de raisonnement complet (j'avais oublié un point important expliqué dans mon classeur.
Je joins un classeur explicatif et je continue à chercher.

Un grand merci à toi et à vous tous,
Amicalement,
Lionel,
 

Pièces jointes

Vote positif 0 Vote négatif
Re : Macro "SI" erreur de compilation

Bonjour Lionel,

Pas tout compris quant à la finalité de ta macro mais j'ai réglé les erreurs qui apparaissaient à l'exécution.

Elles étaient provoquées par une mauvaise utilisation de la commande Copy...Paste.

Voici donc la macro modifiée :

Code: 
Sub Macro1()
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Feuil2.Unprotect
    
  Sheets("SM (2)").Select
    If [k1] = "NL" Then
      Columns("B:L").Copy Destination:=Feuil2.Range("B1")
      Columns("A:L").Delete Shift:=xlToLeft
      For col = 13 To 289 Step 12
        Columns("A:L").Copy Destination:=Feuil2.Range("A1").Offset(0, col)
        Columns("A:L").Delete Shift:=xlToLeft
      Next
      Application.CutCopyMode = False
    End If

    Feuil2.Select
    ActiveWindow.SmallScroll ToRight:=-400
    Range("C1").FormulaR1C1 = _
        "=IF(OR('OF1'!R2C2="""",'OF1'!R2C2=""n° OF""),0,IF('OF1'!RC3=""CAL"",'OF1'!R2C2,0))"
    Range("E1").FormulaR1C1 = "=IF(RC[-2]=0,"""",'OF1'!RC3)"
    Range("G1").FormulaR1C1 = _
        "=IF(RC[-4]=0,"""",CONCATENATE(""Sem"",""  "",'OF1'!R[2]C8))"
    Range("I1").FormulaR1C1 = "=IF(RC[-6]=0,"""",""Qté livrée : "")"
    Range("B2").FormulaR1C1 = "=IF(R[-1]C[1]=0,"""",""Suivi des Rebuts"")"
    Range("E2").FormulaR1C1 = "=IF(R[-1]C[-2]=0,"""",'OF1'!RC5)"
    Range("G2").FormulaR1C1 = "=IF(R[-1]C[-4]=0,"""",""Période du"")"
    Range("I2").FormulaR1C1 = "=IF(R[-1]C[-6]=0,"""",'OF1'!R[1]C10)"
    Range("J2").FormulaR1C1 = "=IF(R[-1]C[-7]=0,"""",""au"")"
    Range("K2").FormulaR1C1 = "=IF(R[-1]C[-8]=0,"""",'OF1'!R[1]C12)"
    Range("L2").FormulaR1C1 = "=IF(RC[-1]<>"""",RC[-1],"""")"
    Range("C3").FormulaR1C1 = "=IF(R[-2]C=0,"""",""Qte/Tps.Un"")"
    Range("D3").FormulaR1C1 = "=IF(R[-2]C[-1]=0,"""",'OF1'!RC4)"
    Range("G3").FormulaR1C1 = "=IF(R[-2]C[-4]=0,"""",""Mon tage"")"
    Range("H3").FormulaR1C1 = "=IF(R[-2]C[-5]=0,"""",""Client"")"
    Range("I3").FormulaR1C1 = "=IF(R[-2]C[-6]=0,"""",""Tampo"")"
    Range("J3").FormulaR1C1 = "=IF(R[-2]C[-7]=0,"""",""total Rebuts"")"
    Range("K3").FormulaR1C1 = "=IF(R[-2]C[-8]=0,"""",""Qté Util."")"
    Range("L3").FormulaR1C1 = "=IF(R[-2]C[-9]=0,"""",""%    rebuts"")"
    Range("C4").FormulaR1C1 = "=IF(R1C3=0,0,ROUND('OF1'!R[1]C3,3))"
    Range("D4").FormulaR1C1 = "=IF(R1C3=0,0,'OF1'!R[1]C4)"
    Range("E4").FormulaR1C1 = "=IF(R1C3=0,0,'OF1'!R[1]C5)"
    Range("F4").FormulaR1C1 = "=IF(R1C3=0,0,'OF1'!R[1]C6)"
    Range("C4:F4").Copy Range("C5:F43")
    Range("J4").FormulaR1C1 = _
        "=IF(AND(RC[-6]=0,SUM(RC[-3]:RC[-1])>0),""Ha Ha  ?"",IF(R1C[1]=""NL"",SUM(RC[-3]:RC[-1]),IF(SUM(RC[-3]:RC[-1])>(R1C[1]*RC[-7]),""    Trop"",SUM(RC[-3]:RC[-1]))))"
    Range("K4").FormulaR1C1 = _
        "=IF(RC[-1]="" Ha Ha ?"",0,IF(RC[-1]=0,0,IF(R1C=""NL"",""NL"",IF(R1C>0,R1C*RC[-8],0))))"
    Range("L4").FormulaR1C1 = _
        "=IF(RC[-2]=""    Trop"",0.00001,IF(AND(RC[-2]=0,RC[-1]=""NL""),0,IF(AND(RC[-2]>0,RC[-1]=""NL""),0.00001,IF(RC[-1]>0,RC[-2]/RC[-1]*100,0))))"
    Range("J4:L4").Copy Range("J5:L43")
    Range("G4:I43").Locked = False
    Selection.FormulaHidden = False
    Range("G4").Select
    Application.CutCopyMode = False
    
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    Application.EnableEvents = True
End Sub
Espérant avoir pu t'aider.

Cordialement.
 
Vote positif 0 Vote négatif
Re : Macro "SI" erreur de compilation

Bonjour Papou-net,
Bonjour à tous,

Oui, tu m'aides beaucoup et j'apprends grâce à toi.
Même si je n'arrive pas à m'expliquer "clairement" LOL ta macro me fait avancer et m'offre une grande partie de la solution recherchée.
Quand j'aurai "digéré" tout ça, je tenterai à nouveau d'être plus clair.
Amicalement,
Lionel,
 
Vote positif 0 Vote négatif
Rejoins nos membres "Supporter XLD" et booste ton expérience Excel !
- Navigue sans publicité
- Accède à Cléa, notre assistante IA experte Excel... et pas que...
- Profite de fonctionnalités exclusives
Ton soutien permet à Excel Downloads de rester 100% gratuit et de continuer à rassembler les passionnés d'Excel.
Je deviens Supporter XLD

Discussions similaires

T
  • Question Question
XL 2013 Impression, choix de feuilles et nombre de copie
Réponses
11
Affichages
613
AtTheOne
AtTheOne
X
  • Question Question
XL 2021 Transformation de données sur une ligne en plusieurs lignes
Réponses
9
Affichages
405
xavieralvaro
X
U
  • Question Question
Autres [RÉSOLU] macro à modifier
Réponses
1
Affichages
505
un internaute
U
richardlion
  • Question Question
XL 2016 copier valeur actuel
2
Réponses
18
Affichages
422
sylvanu
sylvanu
M
  • Question Question
XL 2019 La méthode select de la classe range a échoué
Réponses
4
Affichages
268
job75
job75
N
  • Question Question
XL 2013 Utilisé deux CommandButton_Click dans la même feuil
Réponses
4
Affichages
348
patricktoulon
patricktoulon
V
  • Question Question
XL 2016 transformer en boucle une macro créer avec l'enregistreur automatique
Réponses
3
Affichages
164
vazvaz
V
G
  • Question Question
XL 2019 Sélectionner un fichier pour mettre à jour une base de données
Réponses
3
Affichages
340
sylvanu
sylvanu
arck
  • Résolu(e)
XL 2019 macro avec fonction copié coller si rempli par la macro
Réponses
12
Affichages
424
AtTheOne
AtTheOne
H
  • Résolu(e)
XL 2016 Copier cellule dans un autre onglets
2
Réponses
16
Affichages
778
Phil69970
Phil69970
U
  • Question Question
Autres [RÉSOLU] Compléter macro pour Suppr cellules
Réponses
7
Affichages
333
un internaute
U
U
  • Question Question
Autres [RÉSOLU] Compléter la macro mais comment et où?
Réponses
2
Affichages
567
un internaute
U
ph.vanne
  • Question Question
Autres fenêtre "Publication"
Réponses
10
Affichages
653
ph.vanne
ph.vanne
C
  • Résolu(e)
XL 2021 Macro pour mise en page
Réponses
11
Affichages
743
candido57
C
VBA_dev_Anne_Marie
  • Question Question
Microsoft 365 VBA : copier les adresses
Réponses
7
Affichages
684
VBA_dev_Anne_Marie
VBA_dev_Anne_Marie
R
  • Question Question
Microsoft 365 Import .txt au format date
Réponses
14
Affichages
379
dysorthographie
dysorthographie
F
  • Question Question
Microsoft 365 Problème mémoire insuffisante
Réponses
5
Affichages
699
TooFatBoy
TooFatBoy
T
  • Résolu(e)
Microsoft 365 VBA Exporter une sélection de plages nommées au format PDF
Réponses
12
Affichages
954
tnion
T
G
  • Résolu(e)
XL 2019 macro pour masquer puis afficher colonnes a la demande
Réponses
4
Affichages
261
Phil69970
Phil69970
L
  • Question Question
Microsoft 365 Erreur automation 440 sur code VBA
Réponses
1
Affichages
301
job75
job75
Retour