Macro "SI" erreur de compilation

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,
 

Papou-net

XLDnaute Barbatruc
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.
 

Papou-net

XLDnaute Barbatruc
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.
 

Papou-net

XLDnaute Barbatruc
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.
 

Papou-net

XLDnaute Barbatruc
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.
 

Papou-net

XLDnaute Barbatruc
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.
 

Usine à gaz

XLDnaute Barbatruc
Supporter XLD
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,
 

Usine à gaz

XLDnaute Barbatruc
Supporter XLD
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

  • Test.zip
    129 KB · Affichages: 39
  • Test.zip
    129 KB · Affichages: 41
  • Test.zip
    129 KB · Affichages: 36

Papou-net

XLDnaute Barbatruc
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.
 

Usine à gaz

XLDnaute Barbatruc
Supporter XLD
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,
 

Discussions similaires