Erreur 438

Le 24/03/2018 à 13:52

Bonjour voila j'ai un fichier qui est censer faire le calcule des masses horaires je me suis rendu compte qu'il restait quelque erreur légère mais pour les corriger sa va c'est encore faisable mais suis obliger de le faire 21 fois.

https://www.cjoint.com/c/HCymWymDyYk

    
Sub Macro5()
    ActiveWorkbook.Worksheets("BAout").Names ("durée10"), RefersTo = _
        "=SUM(IFERROR(IF(AND(HAout!$AF$7=31,OR(IFERROR(VLOOKUP(HAout!$AF$2,DimFe,1,FALSE),0),VLOOKUP(HAout!$AF$2,moindimfe,1,FALSE))),SUM(IF(IFERROR(RIGHT(HAout!$AF9,5),0)>""00:00"",""00:00"",IFERROR(RIGHT(HAout!$AF9,5),0)),IF(IFERROR(RIGHT(HAout!$AF9,5),0)<""09:00"",IFERROR(RIGHT(HAout!$AF9,5),0),""00:00""),IF(IFERROR(RIGHT(HAout!$AF9,5),0)<""07:00"",IFERROR(RIGHT(HAout!$AF9" & _
        """07:00"")-IF(IFERROR(RIGHT(HAout!$AF9,5),0)<""07:00"",""07:00"",IFERROR(RIGHT(HAout!$AF9,5),0))),""00:00""),0), IFERROR(IF(AND(HAout!$AF$7<>31,HAout!$AE$7=30,_xlfn.ISFORMULA(HAout!$AE9),OR(IFERROR(VLOOKUP(HAout!$AE$2,DimFe,1,FALSE),0),VLOOKUP(HAout!$AE$2,moindimfe,1,FALSE))),SUM(IF(IFERROR(RIGHT(HAout!$AE9,5),0)>""00:00"",""00:00"",IFERROR(RIGHT(HAout!$AE9,5),0)),I" & _
        "(RIGHT(HAout!$AE9,5),0)<""09:00"",IFERROR(RIGHT(HAout!$AE9,5),0),""00:00""),IF(IFERROR(RIGHT(HAout!$AE9,5),0)<""07:00"",IFERROR(RIGHT(HAout!$AE9,5),0),""7:00"")-IF(IFERROR(RIGHT(HAout!$AE9,5),0)<""07:00"",""7:00"",IFERROR(RIGHT(HAout!$AE9,5),0))),""00:00""),0))"
    ActiveWorkbook.Worksheets("BAout").Names ("Dimnuit4"), RefersTo = _
        "=IF(AND(OR(AND(LEFT(HAout!$S9,5)>""06:59"",RIGHT(HAout!$S9,5)>""21:00""),AND(LEFT(HAout!$S9,5)>""19:00"",RIGHT(HAout!$S9,5)<""8:00"")),ISNUMBER(1*LEFT(HAout!$S9,1)),OR(ISNUMBER(VLOOKUP(HAout!$S$2,DimFe,1,FALSE)),ISNUMBER(VLOOKUP(HAout!$S$2,moindimfe,1,FALSE)))),TEXT(HAout!$S$2,""jj/mm/aa"")&"" - "","""") &IF(AND(OR(AND(LEFT(HAout!$T9,5)>""06:59"",RIGHT(HAou" & _
        ")>""21:00""),AND(LEFT(HAout!$T9,5)>""19:00"",RIGHT(HAout!$T9,5)<""8:00"")),ISNUMBER(1*LEFT(HAout!$T9,1)),OR(ISNUMBER(VLOOKUP(HAout!$T$2,DimFe,1,FALSE)),AND(LEFT(HAout!$T9,5)>""19:00"",RIGHT(HAout!$T9,5)<""8:00""),ISNUMBER(VLOOKUP(HAout!$T$2,moindimfe,1,FALSE)))),TEXT(HAout!$T$2,""jj/mm/aa"")&"" - "","""") &IF(AND(OR(AND(LEFT(HAout!$U9,5)>""06:59"",RIGHT(HAo" & _
        ")>""21:00""),AND(LEFT(HAout!$U9,5)>""19:00"",RIGHT(HAout!$U9,5)<""8:00"")),ISNUMBER(1*LEFT(HAout!$U9,1)),OR(ISNUMBER(VLOOKUP(HAout!$U$2,DimFe,1,FALSE)),AND(LEFT(HAout!$U9,5)>""19:00"",RIGHT(HAout!$U9,5)<""8:00""),ISNUMBER(VLOOKUP(HAout!$U$2,moindimfe,1,FALSE)))),TEXT(HAout!$U$2,""jj/mm/aa"")&"" - "","""") &IF(AND(OR(AND(LEFT(HAout!$V9,5)>""06:59"",RIGHT(HAo" & _
        ")>""21:00""),AND(LEFT(HAout!$V9,5)>""19:00"",RIGHT(HAout!$V9,5)<""8:00"")),ISNUMBER(1*LEFT(HAout!$V9,1)),OR(ISNUMBER(VLOOKUP(HAout!$V$2,DimFe,1,FALSE)),AND(LEFT(HAout!$V9,5)>""19:00"",RIGHT(HAout!$V9,5)<""8:00""),ISNUMBER(VLOOKUP(HAout!$V$2,moindimfe,1,FALSE)))),TEXT(HAout!$V$2,""jj/mm/aa"")&"" - "","""") &IF(AND(OR(AND(LEFT(HAout!$W9,5)>""06:59"",RIGHT(HAo" & _
        ")>""21:00""),AND(LEFT(HAout!$W9,5)>""19:00"",RIGHT(HAout!$W9,5)<""8:00"")),ISNUMBER(1*LEFT(HAout!$W9,1)),OR(ISNUMBER(VLOOKUP(HAout!$W$2,DimFe,1,FALSE)),AND(LEFT(HAout!$W9,5)>""19:00"",RIGHT(HAout!$W9,5)<""8:00""),ISNUMBER(VLOOKUP(HAout!$W$2,moindimfe,1,FALSE)))),TEXT(HAout!$W$2,""jj/mm/aa"")&"" - "","""")"
    ActiveWorkbook.Worksheets("BAout").Names("Dimnuit6").RefersTo = _
        "=IF(AND(OR(AND(LEFT(HAout!$AC9,5)>""06:59"",RIGHT(HAout!$AC9,5)>""21:00""),AND(LEFT(HAout!$AC9,5)>""19:00"",RIGHT(HAout!$AC9,5)<""8:00"")),ESTADUAC(1*LEFT(HAout!$AC9,1)),OR(ESTADUAC(VLOOKUP(HAout!$AC$2,Dimfe,1,FALSE)),ESTADUAC(VLOOKUP(HAout!$AC$2,Moindimfe,1,FALSE)))),TEXT(HAout!$AC$2,""jj/MM/aa"")&"" - "","""") &IF(AND(OR(AND(LEFT(HAout!$AD9,5)>""06:59"",RIGHT(" & _
        "30,5)>""21:00""),AND(LEFT(HAout!$AD9,5)>""19:00"",RIGHT(HAout!$AD9,5)<""8:00"")),ESTADUAC(1*LEFT(HAout!$AD9,1)),OR(ESTADUAC(VLOOKUP(HAout!$AD$2,Dimfe,1,FALSE)),ESTADUAC(VLOOKUP(HAout!$AD$2,Moindimfe,1,FALSE)))),TEXT(HAout!$AD$2,""jj/MM/aa"")&"" - "","""") &IF(AND(OR(AND(LEFT(HAout!$AE9,5)>""06:59"",RIGHT(HAout!$AE9,5)>""21:00""),AND(LEFT(HAout!$AE9,5)>""19:00""," & _
        "ut!$AE9,5)<""8:00"")),ESTADUAC(1*LEFT(HAout!$AE9,1)),OR(ESTADUAC(VLOOKUP(HAout!$AE$2,Dimfe,1,FALSE)),ESTADUAC(VLOOKUP(HAout!$AE$2,Moindimfe,1,FALSE)))),TEXT(HAout!$AE$2,""jj/MM/aa"")&"" - "","""") &IF(AND(OR(AND(LEFT(HAout!$AF9,5)>""06:59"",RIGHT(HAout!$AF9,5)>""21:00""),AND(LEFT(HAout!$AF9,5)>""19:00"",RIGHT(HAout!$AF9,5)<""8:00"")),ESTADUAC(1*LEFT(HAout!$AF9,1" & _
        "ADUAC(VLOOKUP(HAout!$AF$2,Dimfe,1,FALSE)),ESTADUAC(VLOOKUP(HAout!$AF$2,Moindimfe,1,FALSE)))),TEXT(HAout!$AF$2,""jj/MM/aa"")&"" - "","""") &IF(AND(OR(AND(LEFT(HAout!$AE9,5)>""06:59"",RIGHT(HAout!$AE9,5)>""21:00""),AND(LEFT(HAout!$AE9,5)>""19:00"",RIGHT(HAout!$AE9,5)<""8:00"")),ESTADUAC(1*LEFT(HAout!$AE9,1)),OR(ESTADUAC(VLOOKUP(HAout!$AE$2,Dimfe,1,FALSE)),ESTADUA" & _
        "(HAout!$AE$2,Moindimfe,1,FALSE)))),TEXT(HAout!$AE$2,""jj/MM/aa"")&"" - "","""") &IF(AND(OR(AND(LEFT(HAout!$AF9,5)>""06:59"",RIGHT(HAout!$AF9,5)>""21:00""),AND(LEFT(HAout!$AF9,5)>""19:00"",RIGHT(HAout!$AF9,5)<""8:00"")),ESTADUAC(1*LEFT(HAout!$AF9,1)),OR(ESTADUAC(VLOOKUP(HAout!$AF$2,Dimfe,1,FALSE)),ESTADUAC(VLOOKUP(HAout!$AF$2,Moindimfe,1,FALSE)))),TEXT(HAout!R" & _
        "/MM/aa"")&"" - "","""")"
    ActiveWorkbook.Worksheets("BAout").Names("Dimnuit6").RefersTo = _
        "=IF(AND(OR(AND(LEFT(HAout!$AC9,5)>""06:59"",RIGHT(HAout!$AC9,5)>""21:00""),AND(LEFT(HAout!$AC9,5)>""19:00"",RIGHT(HAout!$AC9,5)<""8:00"")),ESTADUAC(1*LEFT(HAout!$AC9,1)),OR(ESTADUAC(VLOOKUP(HAout!$AC$2,Dimfe,1,FALSE)),ESTADUAC(VLOOKUP(HAout!$AC$2,Moindimfe,1,FALSE)))),TEXT(HAout!$AC$2,""jj/MM/aa"")&"" - "","""") &IF(AND(OR(AND(LEFT(HAout!$AD9,5)>""06:59"",RIGHT(" & _
        "30,5)>""21:00""),AND(LEFT(HAout!$AD9,5)>""19:00"",RIGHT(HAout!$AD9,5)<""8:00"")),ESTADUAC(1*LEFT(HAout!$AD9,1)),OR(ESTADUAC(VLOOKUP(HAout!$AD$2,Dimfe,1,FALSE)),ESTADUAC(VLOOKUP(HAout!$AD$2,Moindimfe,1,FALSE)))),TEXT(HAout!$AD$2,""jj/MM/aa"")&"" - "","""") &IF(AND(OR(AND(LEFT(HAout!$AE9,5)>""06:59"",RIGHT(HAout!$AE9,5)>""21:00""),AND(LEFT(HAout!$AE9,5)>""19:00""," & _
        "ut!$AE9,5)<""8:00"")),ESTADUAC(1*LEFT(HAout!$AE9,1)),OR(ESTADUAC(VLOOKUP(HAout!$AE$2,Dimfe,1,FALSE)),ESTADUAC(VLOOKUP(HAout!$AE$2,Moindimfe,1,FALSE)))),TEXT(HAout!$AE$2,""jj/MM/aa"")&"" - "","""") &IF(AND(OR(AND(LEFT(HAout!$AF9,5)>""06:59"",RIGHT(HAout!$AF9,5)>""21:00""),AND(LEFT(HAout!$AF9,5)>""19:00"",RIGHT(HAout!$AF9,5)<""8:00"")),ESTADUAC(1*LEFT(HAout!$AF9,1" & _
        "ADUAC(VLOOKUP(HAout!$AF$2,Dimfe,1,FALSE)),ESTADUAC(VLOOKUP(HAout!$AF$2,Moindimfe,1,FALSE)))),TEXT(HAout!$AF$2,""jj/MM/aa"")&"" - "","""") &IF(AND(OR(AND(LEFT(HAout!$AE9,5)>""06:59"",RIGHT(HAout!$AE9,5)>""21:00""),AND(LEFT(HAout!$AE9,5)>""19:00"",RIGHT(HAout!$AE9,5)<""8:00"")),ESTADUAC(1*LEFT(HAout!$AE9,1)),OR(ESTADUAC(VLOOKUP(HAout!$AE$2,Dimfe,1,FALSE)),ESTADUA" & _
        "(HAout!$AE$2,Moindimfe,1,FALSE)))),TEXT(HAout!$AE$2,""jj/MM/aa"")&"" - "","""") &IF(AND(OR(AND(LEFT(HAout!$AF9,5)>""06:59"",RIGHT(HAout!$AF9,5)>""21:00""),AND(LEFT(HAout!$AF9,5)>""19:00"",RIGHT(HAout!$AF9,5)<""8:00"")),ESTADUAC(1*LEFT(HAout!$AF9,1)),OR(ESTADUAC(VLOOKUP(HAout!$AF$2,Dimfe,1,FALSE)),ESTADUAC(VLOOKUP(HAout!$AF$2,Moindimfe,1,FALSE)))),TEXT(HAout!R" & _
        "/MM/aa"")&"" - "","""")"
    ActiveWorkbook.Worksheets("BAout").Names("Dimnuit6").RefersTo = _
        "=IF(AND(OR(AND(LEFT(HAout!$AC9,5)>""06:59"",RIGHT(HAout!$AC9,5)>""21:00""),AND(LEFT(HAout!$AC9,5)>""19:00"",RIGHT(HAout!$AC9,5)<""8:00"")),ESTADUAC(1*LEFT(HAout!$AC9,1)),OR(ESTADUAC(VLOOKUP(HAout!$AC$2,Dimfe,1,FALSE)),ESTADUAC(VLOOKUP(HAout!$AC$2,Moindimfe,1,FALSE)))),TEXT(HAout!$AC$2,""jj/MM/aa"")&"" - "","""") &IF(AND(OR(AND(LEFT(HFevri" & _
        "5)>""06:59"",RIGHT(HAout!$AD9,5)>""21:00""),AND(LEFT(HAout!$AD9,5)>""19:00"",RIGHT(HAout!$AD9,5)<""8:00"")),ESTADUAC(1*LEFT(HAout!$AD9,1)),OR(ESTADUAC(VLOOKUP(HAout!$AD$2,Dimfe,1,FALSE)),ESTADUAC(VLOOKUP(HAout!$AD$2,Moindimfe,1,FALSE)))),TEXT(HAout!$AD$2,""jj/MM/aa"")&"" - "","""") &IF(AND(OR(AND(LEFT(HAout!$AE9,5)>""06:59"",RIGHT(HFevrie" & _
        ">""21:00""),AND(LEFT(HAout!$AE9,5)>""19:00"",RIGHT(HAout!$AE9,5)<""8:00"")),ESTADUAC(1*LEFT(HAout!$AE9,1)),OR(ESTADUAC(VLOOKUP(HAout!$AE$2,Dimfe,1,FALSE)),ESTADUAC(VLOOKUP(HAout!$AE$2,Moindimfe,1,FALSE)))),TEXT(HAout!$AE$2,""jj/MM/aa"")&"" - "","""") &IF(AND(OR(AND(LEFT(HAout!$AF9,5)>""06:59"",RIGHT(HAout!$AF9,5)>""21:00""),AND(LEFT(HFevr" & _
        "5)>""19:00"",RIGHT(HAout!$AF9,5)<""8:00"")),ESTADUAC(1*LEFT(HAout!$AF9,1)),OR(ESTADUAC(VLOOKUP(HAout!$AF$2,Dimfe,1,FALSE)),ESTADUAC(VLOOKUP(HAout!$AF$2,Moindimfe,1,FALSE)))),TEXT(HAout!$AF$2,""jj/MM/aa"")&"" - "","""") &IF(AND(OR(AND(LEFT(HAout!$AE9,5)>""06:59"",RIGHT(HAout!$AE9,5)>""21:00""),AND(LEFT(HAout!$AE9,5)>""19:00"",RIGHT(HFevrie" & _
        "<""8:00"")),ESTADUAC(1*LEFT(HAout!$AE9,1)),OR(ESTADUAC(VLOOKUP(HAout!$AE$2,Dimfe,1,FALSE)),ESTADUAC(VLOOKUP(HAout!$AE$2,Moindimfe,1,FALSE)))),TEXT(HAout!$AE$2,""jj/MM/aa"")&"" - "","""") &IF(AND(OR(AND(LEFT(HAout!$AF9,5)>""06:59"",RIGHT(HAout!$AF9,5)>""21:00""),AND(LEFT(HAout!$AF9,5)>""19:00"",RIGHT(HAout!$AF9,5)<""8:00"")),ESTADUAC(1*LEF" & _
        "r!$AF9,1)),OR(ESTADUAC(VLOOKUP(HAout!$AF$2,Dimfe,1,FALSE)),ESTADUAC(VLOOKUP(HAout!$AF$2,Moindimfe,1,FALSE)))),TEXT(HAout!$AF$2,""jj/MM/aa"")&"" - "","""")"
    ActiveWorkbook.Worksheets("BAout").Names("Dimnuit").RefersTo = _
        "=IFERROR(BAout!nuitmoismoindim&""""&BAout!Dimnuit1&""""&BAout!Dimnuit2&""""&BAout!Dimnuit3&""""&BAout!Dimnuit4&""""&BAout!Dimnuit5&""""&BAout!Dimnuit6,"""")"
    ActiveWorkbook.Worksheets("BAout").Names("semainenuit").RefersTo = _
        "=IFERROR(BAout!nuitmoismoins&""""&BAout!semainenuit1&""""&BAout!semainenuit2&""""&BAout!semainenuit3&""""&BAout!semainenuit4&""""&BAout!semainenuit5&""""&BAout!semainenuit6,"""")"
    ActiveWorkbook.Worksheets("BAout").Names("nuitmoismoindim").RefersTo = _
        "=IF(AND(HJuillet!$AF$7=31,AND(LEFT(HJuillet!$AF9,5)>""19:00"",RIGHT(HJuillet!$AF9,5)<""8:00""),ISNUMBER(1*LEFT(HJuillet!$AF9,1)),ISNUMBER(VLOOKUP(HJuillet!$AF$2,DimFe,1,FALSE))),TEXT(HJuillet!$AF$2,""jj/mm/aa"")&"" - "","""")& IF(AND(AND(HJuillet!$AE$7=30,HJuillet!$AF$7=""""),AND(LEFT(HJuillet!$AE9,5)>""19:00"",RIGHT(HJuillet!$AE9,5)<""" & _
        "ISNUMBER(1*LEFT(HJuillet!$AE9,1)),ISNUMBER(VLOOKUP(HJuillet!$AE$2,DimFe,1,FALSE))),TEXT(HJuillet!$AE$2,""jj/mm/aa"")&"" - "","""")"
 End SUB
Le 24/03/2018 à 16:12

Bonjour,

Il est préférable de déposer le fichier ici et par sur cjoint où il ne restera pas...

Je débroussaille pour les spécialistes, car les formules sont un peu trop complexes pour mon neurone...

P.

Le 24/03/2018 à 16:34

Bonjour patrick1957,

Finie la couleur bleu ciel ? on préfère le vert clair ?

Pour moi aussi, les formules de Keran Latos sont un peu trop complexes pour mon goût ! je les trouve un tantinet insipides, et j'lui souhaite bon courage pour arriver à s'dépatouiller avec !

J'tai mis un ♡ pour le désherbage débroussaillage. (et merci pour le tien)

dhany

Rechercher des sujets similaires à "erreur 438"