[cloturer]erreur 1007

K

Keran

XLDnaute Junior
Bonjour voila j'ai une vba que j'aimerais executer mais elle me dit erreur 1007 je ne trouve pas pourquoi :/
VB: 
Sub Macro7()
'
' Macro7 Macro
'

'
    ActiveWorkbook.Worksheets("BAout").Names("nuitmoismoindim").RefersToR1C1 = _
        "=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$2=30,HJuillet!$AF$7=""""),AND(LEFT(HJuillet!$AE9,5)>""19:00"",RIGHT(HJuillet!$AE9,5)<""8:00""),ISNUMBER(1*LEFT(HJuillet" & _
        "!$AE9,1)),ISNUMBER(VLOOKUP(HJuillet!$AE$2,DimFe,1,FALSE))),TEXT(HJuillet!$AE$2,""jj/mm/aa"")&"" - "","""")"""
       
    ActiveWorkbook.Worksheets("BAout").Names("nuitmoismoins").RefersToR1C1 = _
        "=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$2=30,HJuillet!$AF$7=""""),AND(LEFT(HJuillet!$AE9,5)>""19:00"",RIGHT(HJuillet!$AE9,5)<""8:00""),ISNUMBER(1*LEFT(HJuillet" & _
        "et!$AE9,1)),ISNUMBER(VLOOKUP(HJuillet!$AE$2,DimFe,1,FALSE))),TEXT(HJuillet!$AE$2,""jj/mm/aa"")&"" - "","""")"""
    '
    ActiveWorkbook.Worksheets("BAvril").Names("nuitmoismoindim").RefersToR1C1 = _
        "=IF(AND(HMars!$AF$7=31,AND(LEFT(HMars!$AF9,5)>""19:00"",RIGHT(HMars!$AF9,5)<""8:00""),ISNUMBER(1*LEFT(HMars!$AF9,1)),ISNUMBER(VLOOKUP(HMars!$AF$2,DimFe,1,FALSE))),TEXT(HMars!$AF$2,""jj/mm/aa"")&"" - "","""")& IF(AND(AND(HMars!$AE$2=30,HMars!$AF$7=""""),AND(LEFT(HMars!$AE9,5)>""19:00"",RIGHT(HMars!$AE9,5)<""8:00""),ISNUMBER(1*LEFT(HMars" & _
        "!$AE9,1)),ISNUMBER(VLOOKUP(HMars!$AE$2,DimFe,1,FALSE))),TEXT(HMars!$AE$2,""jj/mm/aa"")&"" - "","""")"""
    ActiveWorkbook.Worksheets("BAvril").Names("nuitmoismoins").RefersToR1C1 = _
        "=IF(AND(HMars!$AF$7=31,AND(LEFT(HMars!$AF9,5)>""19:00"",RIGHT(HMars!$AF9,5)<""8:00""),ISNUMBER(1*LEFT(HMars!$AF9,1)),ISNUMBER(VLOOKUP(HMars!$AF$2,DimFe,1,FALSE))),TEXT(HMars!$AF$2,""jj/mm/aa"")&"" - "","""")& IF(AND(AND(HMars!$AE$2=30,HMars!$AF$7=""""),AND(LEFT(HMars!$AE9,5)>""19:00"",RIGHT(HMars!$AE9,5)<""8:00""),ISNUMBER(1*LEFT(HMars" & _
        "et!$AE9,1)),ISNUMBER(VLOOKUP(HMars!$AE$2,DimFe,1,FALSE))),TEXT(HMars!$AE$2,""jj/mm/aa"")&"" - "","""")"""
    '"
    ActiveWorkbook.Worksheets("BDecembre").Names("nuitmoismoindim").RefersToR1C1 = _
        "=IF(AND(HNovembre!$AF$7=31,AND(LEFT(HNovembre!$AF9,5)>""19:00"",RIGHT(HNovembre!$AF9,5)<""8:00""),ISNUMBER(1*LEFT(HNovembre!$AF9,1)),ISNUMBER(VLOOKUP(HNovembre!$AF$2,DimFe,1,FALSE))),TEXT(HNovembre!$AF$2,""jj/mm/aa"")&"" - "","""")& IF(AND(AND(HNovembre!$AE$2=30,HNovembre!$AF$7=""""),AND(LEFT(HNovembre!$AE9,5)>""19:00"",RIGHT(HNovembre!$AE9,5)<""8:00""),ISNUMBER(1*LEFT(HNovembre" & _
        "!$AE9,1)),ISNUMBER(VLOOKUP(HNovembre!$AE$2,DimFe,1,FALSE))),TEXT(HNovembre!$AE$2,""jj/mm/aa"")&"" - "","""")"
    ActiveWorkbook.Worksheets("BDecembre").Names("nuitmoismoins").RefersToR1C1 = _
        "=IF(AND(HNovembre!$AF$7=31,AND(LEFT(HNovembre!$AF9,5)>""19:00"",RIGHT(HNovembre!$AF9,5)<""8:00""),ISNUMBER(1*LEFT(HNovembre!$AF9,1)),ISNUMBER(VLOOKUP(HNovembre!$AF$2,DimFe,1,FALSE))),TEXT(HNovembre!$AF$2,""jj/mm/aa"")&"" - "","""")& IF(AND(AND(HNovembre!$AE$2=30,HNovembre!$AF$7=""""),AND(LEFT(HNovembre!$AE9,5)>""19:00"",RIGHT(HNovembre!$AE9,5)<""8:00""),ISNUMBER(1*LEFT(HNovembre" & _
        "et!$AE9,1)),ISNUMBER(VLOOKUP(HNovembre!$AE$2,DimFe,1,FALSE))),TEXT(HNovembre!$AE$2,""jj/mm/aa"")&"" - "","""")"
    '
    ActiveWorkbook.Worksheets("BJuillet").Names("nuitmoismoindim").RefersToR1C1 = _
        "=IF(AND(HJuin!$AF$7=31,AND(LEFT(HJuin!$AF9,5)>""19:00"",RIGHT(HJuin!$AF9,5)<""8:00""),ISNUMBER(1*LEFT(HJuin!$AF9,1)),ISNUMBER(VLOOKUP(HJuin!$AF$2,DimFe,1,FALSE))),TEXT(HJuin!$AF$2,""jj/mm/aa"")&"" - "","""")& IF(AND(AND(HJuin!$AE$2=30,HJuin!$AF$7=""""),AND(LEFT(HJuin!$AE9,5)>""19:00"",RIGHT(HJuin!$AE9,5)<""8:00""),ISNUMBER(1*LEFT(HJuin" & _
        "!$AE9,1)),ISNUMBER(VLOOKUP(HJuin!$AE$2,DimFe,1,FALSE))),TEXT(HJuin!$AE$2,""jj/mm/aa"")&"" - "","""")" & _
        ""
    ActiveWorkbook.Worksheets("BJuillet").Names("nuitmoismoins").RefersToR1C1 = _
        "=IF(AND(HJuin!$AF$7=31,AND(LEFT(HJuin!$AF9,5)>""19:00"",RIGHT(HJuin!$AF9,5)<""8:00""),ISNUMBER(1*LEFT(HJuin!$AF9,1)),ISNUMBER(VLOOKUP(HJuin!$AF$2,DimFe,1,FALSE))),TEXT(HJuin!$AF$2,""jj/mm/aa"")&"" - "","""")& IF(AND(AND(HJuin!$AE$2=30,HJuin!$AF$7=""""),AND(LEFT(HJuin!$AE9,5)>""19:00"",RIGHT(HJuin!$AE9,5)<""8:00""),ISNUMBER(1*LEFT(HJuin" & _
        "et!$AE9,1)),ISNUMBER(VLOOKUP(HJuin!$AE$2,DimFe,1,FALSE))),TEXT(HJuin!$AE$2,""jj/mm/aa"")&"" - "","""")" & _
        ""
    '
    ActiveWorkbook.Worksheets("BJuin").Names("nuitmoismoindim").RefersToR1C1 = _
        "=IF(AND(HMai!$AF$7=31,AND(LEFT(HMai!$AF9,5)>""19:00"",RIGHT(HMai!$AF9,5)<""8:00""),ISNUMBER(1*LEFT(HMai!$AF9,1)),ISNUMBER(VLOOKUP(HMai!$AF$2,DimFe,1,FALSE))),TEXT(HMai!$AF$2,""jj/mm/aa"")&"" - "","""")& IF(AND(AND(HMai!$AE$2=30,HMai!$AF$7=""""),AND(LEFT(HMai!$AE9,5)>""19:00"",RIGHT(HMai!$AE9,5)<""8:00""),ISNUMBER(1*LEFT(HMai" & _
        "!$AE9,1)),ISNUMBER(VLOOKUP(HMai!$AE$2,DimFe,1,FALSE))),TEXT(HMai!$AE$2,""jj/mm/aa"")&"" - "","""")" & _
        ""
    ActiveWorkbook.Worksheets("BJuin").Names("nuitmoismoins").RefersToR1C1 = _
        "=IF(AND(HMai!$AF$7=31,AND(LEFT(HMai!$AF9,5)>""19:00"",RIGHT(HMai!$AF9,5)<""8:00""),ISNUMBER(1*LEFT(HMai!$AF9,1)),ISNUMBER(VLOOKUP(HMai!$AF$2,DimFe,1,FALSE))),TEXT(HMai!$AF$2,""jj/mm/aa"")&"" - "","""")& IF(AND(AND(HMai!$AE$2=30,HMai!$AF$7=""""),AND(LEFT(HMai!$AE9,5)>""19:00"",RIGHT(HMai!$AE9,5)<""8:00""),ISNUMBER(1*LEFT(HMai" & _
        "et!$AE9,1)),ISNUMBER(VLOOKUP(HMai!$AE$2,DimFe,1,FALSE))),TEXT(HMai!$AE$2,""jj/mm/aa"")&"" - "","""")" & _
        ""
    '
     ActiveWorkbook.Worksheets("BMai").Names("nuitmoismoindim").RefersToR1C1 = _
        "=IF(AND(HAvril!$AF$7=31,AND(LEFT(HAvril!$AF9,5)>""19:00"",RIGHT(HAvril!$AF9,5)<""8:00""),ISNUMBER(1*LEFT(HAvril!$AF9,1)),ISNUMBER(VLOOKUP(HAvril!$AF$2,DimFe,1,FALSE))),TEXT(HAvril!$AF$2,""jj/mm/aa"")&"" - "","""")& IF(AND(AND(HAvril!$AE$2=30,HAvril!$AF$7=""""),AND(LEFT(HAvril!$AE9,5)>""19:00"",RIGHT(HAvril!$AE9,5)<""8:00""),ISNUMBER(1*LEFT(HAvril" & _
        "!$AE9,1)),ISNUMBER(VLOOKUP(HAvril!$AE$2,DimFe,1,FALSE))),TEXT(HAvril!$AE$2,""jj/mm/aa"")&"" - "","""")" & _
        ""
    ActiveWorkbook.Worksheets("BMai").Names("nuitmoismoins").RefersToR1C1 = _
        "=IF(AND(HAvril!$AF$7=31,AND(LEFT(HAvril!$AF9,5)>""19:00"",RIGHT(HAvril!$AF9,5)<""8:00""),ISNUMBER(1*LEFT(HAvril!$AF9,1)),ISNUMBER(VLOOKUP(HAvril!$AF$2,DimFe,1,FALSE))),TEXT(HAvril!$AF$2,""jj/mm/aa"")&"" - "","""")& IF(AND(AND(HAvril!$AE$2=30,HAvril!$AF$7=""""),AND(LEFT(HAvril!$AE9,5)>""19:00"",RIGHT(HAvril!$AE9,5)<""8:00""),ISNUMBER(1*LEFT(HAvril" & _
        "et!$AE9,1)),ISNUMBER(VLOOKUP(HAvril!$AE$2,DimFe,1,FALSE))),TEXT(HAvril!$AE$2,""jj/mm/aa"")&"" - "","""")" & _
        ""
    '
     ActiveWorkbook.Worksheets("BMars").Names("nuitmoismoindim").RefersToR1C1 = _
        "=IF(AND(HFevrier!$AF$7=31,AND(LEFT(HFevrier!$AF9,5)>""19:00"",RIGHT(HFevrier!$AF9,5)<""8:00""),ISNUMBER(1*LEFT(HFevrier!$AF9,1)),ISNUMBER(VLOOKUP(HFevrier!$AF$2,DimFe,1,FALSE))),TEXT(HFevrier!$AF$2,""jj/mm/aa"")&"" - "","""")& IF(AND(AND(HFevrier!$AE$2=30,HFevrier!$AF$7=""""),AND(LEFT(HFevrier!$AE9,5)>""19:00"",RIGHT(HFevrier!$AE9,5)<""8:00""),ISNUMBER(1*LEFT(HFevrier" & _
        "!$AE9,1)),ISNUMBER(VLOOKUP(HFevrier!$AE$2,DimFe,1,FALSE))),TEXT(HFevrier!$AE$2,""jj/mm/aa"")&"" - "","""")" & _
        ""
    ActiveWorkbook.Worksheets("BMars").Names("nuitmoismoins").RefersToR1C1 = _
        "=IF(AND(HFevrier!$AF$7=31,AND(LEFT(HFevrier!$AF9,5)>""19:00"",RIGHT(HFevrier!$AF9,5)<""8:00""),ISNUMBER(1*LEFT(HFevrier!$AF9,1)),ISNUMBER(VLOOKUP(HFevrier!$AF$2,DimFe,1,FALSE))),TEXT(HFevrier!$AF$2,""jj/mm/aa"")&"" - "","""")& IF(AND(AND(HFevrier!$AE$2=30,HFevrier!$AF$7=""""),AND(LEFT(HFevrier!$AE9,5)>""19:00"",RIGHT(HFevrier!$AE9,5)<""8:00""),ISNUMBER(1*LEFT(HFevrier" & _
        "et!$AE9,1)),ISNUMBER(VLOOKUP(HFevrier!$AE$2,DimFe,1,FALSE))),TEXT(HFevrier!$AE$2,""jj/mm/aa"")&"" - "","""")" & _
        ""
    '


End Sub


merci d'avance
 
Trier par date Trier par votes
Staple1600

Staple1600

XLDnaute Barbatruc
Re

@Keran
Ici aussi, il faut supprimer ton fichier

Petit aide-mémoire (pour tes prochains posts)
(extrait de la charte du forum)
5 – La possibilité de joindre des fichiers est donnée sur ce forum. Ne pas hésiter à utiliser cette fonction, tout en veillant que les données soient bidons et donc qu’aucune donnée confidentielle, nominative ne soit dans le fichier.
Cliquez pour agrandir...
 
Vote positif 0 Vote négatif
K

Keran

XLDnaute Junior
je pensais avoir supprimer le post juste apres que tu me l'est dit. mes sincère excuse de te déranger autant ^^"


Edit je vais peu etre reussir a finir par faire les choses correctement cette fois.
Il y'a la mon fichier planning qui normalement confidentiel
et ma macro au complet je met ici un extrait aussi .
VB: 
Sub Macro5()
'
' Macro5 Macro
'

'
  ActiveWorkbook.Worksheets("BAout").Names("durée10").RefersToA1 = _
  "=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").RefersToA1 = _
  "=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").RefersToA1 = _
  "=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").RefersToA1 = _
  "=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").RefersToA1 = _
  "=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").RefersToA1 = _
  "=IFERROR(BAout!nuitmoismoindim&""""&BAout!Dimnuit1&""""&BAout!Dimnuit2&""""&BAout!Dimnuit3&""""&BAout!Dimnuit4&""""&BAout!Dimnuit5&""""&BAout!Dimnuit6,"""")"

end Sub

voila j'ai remarquer quelque bug dans mon fichier le hic c'est que pour corriger ce bug sur un fichier c'est disont "rapide" mais je doit reproduire cette operation une vingtaine de fois , j'ai enregistrer mes formules dans le gestonnaire de nom et du coup sa devients tres charger (oui je sais j'ai pas choisi au plus simple au premiers a bord)

donc je me suis dit que je pourrais creer une maccro certe longue mais plus rapide et surtout plus sur de pas ce tromper .

Merci encore pour tous aide ou conseil
 

Pièces jointes

  • Planning.xlsm
    2.4 MB · Affichages: 28
  • Sub Macro5.txt
    112.3 KB · Affichages: 13
Dernière édition:
Vote positif 0 Vote négatif
K

Keran

XLDnaute Junior
sa devrais donner quelque que chose comme sa mais sa marche toujours pas ^^'
VB: 
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
 
Dernière édition:
Vote positif 0 Vote négatif
eriiic

eriiic

XLDnaute Barbatruc
@job75
j'ai précisé en vba, ta documentation concerne les limitations d'excel.
Avec ça (255 caractères + le ' dans une cellule) :
VB: 
Sub Macro1()
    ActiveWorkbook.Names("test").RefersTo = "=SUM(A1,A21)+SUM(A1,A2)+SUM(A1,A2)+SUM(A1,A2)+SUM(A1,A2)+SUM(A1,A2)+SUM(A1,A2)+SUM(A1,A2)+SUM(A1,A2)+SUM(A1,A2)+SUM(A1,A2)+SUM(A1,A2)+SUM(A1,A2)+SUM(A1,A2)+SUM(A1,A2)+SUM(A1,A2)+SUM(A1,A2)+SUM(A1,A2)+SUM(A1,A2)+SUM(A1,A2)+SUM(A1)+SUM(A11)+SUM(A11)+SUM(A1)"
End Sub
ça passe.
Rajoute 1 caractère à une référence => Erreur d'exécution 1004 (tout au moins sur 2010)
eric
 
Dernière édition:
Vote positif 0 Vote négatif
job75

job75

XLDnaute Barbatruc
Re,

En fait c'est la propriété RefersTo qui bloque.

On ne peut pas y écrire plus de 255 caractères.

Avec la méthode Add pas de problème :
Code: 
Sub Macro1()
x = "=SUM(A21,A21)+SUM(A21,A21)+SUM(A1,A2)+SUM(A1,A2)+SUM(A1,A2)+SUM(A1,A2)+SUM(A1,A2)+SUM(A1,A2)+SUM(A1,A2)+SUM(A1,A2)+SUM(A1,A2)+SUM(A1,A2)+SUM(A1,A2)+SUM(A1,A2)+SUM(A1,A2)+SUM(A1,A2)+SUM(A1,A2)+SUM(A1,A2)+SUM(A1,A2)+SUM(A1,A2)+SUM(A1)+SUM(A11)+SUM(A11)+SUM(A1)"
MsgBox Len(x)
ActiveWorkbook.Names.Add "test", x
MsgBox Len(ActiveWorkbook.Names("test").RefersTo) 'lecture
End Sub
Pas de problème non plus pour lire la formule du nom avec RefersTo.

Donc Keran peut s'en sortir avec la méthode Add.

A condition que les formules soient bonnes... J'ai vérifié les 1ères formules : il manque des parenthèses.

A+
 
Vote positif 0 Vote négatif

Discussions similaires

K
  • Question
Erreur '438'
Réponses
6
Affichages
689
Keran
K
K
  • Question
XL 2016 liee deux macro
Réponses
3
Affichages
833
Keran
K
GADENSEB
  • Question
Microsoft 365 Simplification de code
Réponses
1
Affichages
1 K
GADENSEB
GADENSEB
K
  • Question
XL 2016 VBA - Filtre + calcul.
Réponses
2
Affichages
692
Keran
K
Z
  • Question
[Résolu]Problème de changement de fenêtre (PDF / EXCEL)
Réponses
3
Affichages
7 K
Compte Supprimé 979
C
D
  • Question
Incompatibilité de type - Erreur 13
Réponses
5
Affichages
2 K
mromain
mromain
D
  • Question
Importation des données d'un fichier txt versexcel en utilisant une macro
Réponses
2
Affichages
1 K
DadouK
D
V
  • Question
Multiselect et textbox
Réponses
0
Affichages
2 K
vassili
V
_matt_44
  • Question
[MACRO] Erreur 400
Réponses
4
Affichages
2 K
_matt_44
_matt_44
_matt_44
  • Question
[MACRO] Transfer de excel 2003 vers excel 2007
Réponses
3
Affichages
4 K
_matt_44
_matt_44

Membres actuellement en ligne

3 membres et 84 invités.

Partager cette page

Tutos vidéos

Power Query : Ajouter des requêtes (Excel et Power BI)
Créez votre première fonction LAMBDA dans Excel en 3 étapes!
LAMBDA, l’arme secrète de Microsoft pour Excel !
Office Insider - Découvrez les nouveautés Excel avant tout le monde

Statistiques des forums

Discussions
312 196
Messages
2 086 101
Membres
103 116
dernier inscrit
kutobi87