Macro "SI" erreur de compilation

Usine à gaz · 13 Mai 2013

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 · 13 Mai 2013

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 · 13 Mai 2013

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.

Usine à gaz · 13 Mai 2013

Re : Macro "SI" erreur de compilation

Bonsoir Papou-net,

Un grand merci, je vais essayer ton code et surtout essayer de la comprendre LOL.
(j'avais pas compris ton premier message)
Amicalement,
Lionel,

Papou-net · 13 Mai 2013

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.

Usine à gaz · 13 Mai 2013

Re : Macro "SI" erreur de compilation

Bordel !!!
je ne serai jamais capable de faire ça ......
Merci beaucoup,
Amicalement,
Lionel,

Papou-net · 13 Mai 2013

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.

Usine à gaz · 13 Mai 2013

Re : Macro "SI" erreur de compilation

Merci pour l'observation concernant le End If manquant.
Oui dès que j'aurai testé tes codes, je fais mon rapport.
Cordialement,

Papou-net · 13 Mai 2013

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 · 13 Mai 2013

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 · 14 Mai 2013

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,

Papou-net · 16 Mai 2013

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 · 16 Mai 2013

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,

Usine à gaz · 21 Mai 2013

Re : Macro "SI" erreur de compilation

Bonjour Papou-net,
Bonjour à tous,

Ayant été absent, je n'ai pas pu tester ton code.
je vais le faire rapidement et je reviens vers toi.
Amicalement,
Lionel,

Effectuez une recherche sur Excel Downloads...

Macro "SI" erreur de compilation

Usine à gaz

XLDnaute Barbatruc

Papou-net

XLDnaute Barbatruc

Papou-net

XLDnaute Barbatruc

Usine à gaz

XLDnaute Barbatruc

Papou-net

XLDnaute Barbatruc

Usine à gaz

XLDnaute Barbatruc

Papou-net

XLDnaute Barbatruc

Usine à gaz

XLDnaute Barbatruc

Papou-net

XLDnaute Barbatruc

Usine à gaz

XLDnaute Barbatruc

Usine à gaz

XLDnaute Barbatruc

Pièces jointes

Papou-net

XLDnaute Barbatruc

Usine à gaz

XLDnaute Barbatruc

Usine à gaz

XLDnaute Barbatruc

Discussions similaires

Membres actuellement en ligne

Partager cette page

Tutos vidéos

Statistiques des forums