XL 2010 Nb jours ouvres VBA entre des plages nommées

[Profane]

Bonjour à tous

j'ai nommé dynamiquement 3 plages de données:

• DateReception
• DateReparation
• JOURSFERIES

je souhaite renseigner sur une plage(Test) l'opération suivante:

For each c in Range("Test")
c.Offset(, 1).Formula = "=if(" & c.Offset.Addressxxxx" = " ""Oui""; NB.Jours.ouvres("DateReception","DateReparation","JOURSFERIES");""0"";"

comment puis je coder cette affaire ? (sans passer par des Offsets supplémentaires sachant que mes colonnes peuvent être placer différemment suivant le fichier importé)

merci d'avance pour votre aide

 

[Bebere]

bonjour
Profane une pièce jointe serait bienvenue

 

[Profane]

Bonjour bebere
le fichier exemple ci joint
Cordialement

 

[Paf]

Bonjour Profane, Bebere

un essai par fonction personnalisée :

en G2 (par exemple) : =Jour_Ouvré() à tirer vers le bas

et dans un module standard:

Function Jour_Ouvré()
lig = Application.ThisCell.Row - 1 ' à adapter
Rec = Range("DateReception")(lig)
Rep = Range("DateReparation")(lig)
If Range("test")(lig) = "oui" Then
    Jour_Ouvré = Evaluate("NB.JOURS.OUVRES(""" & Rec & """,""" & Rep & """,JOURSFERIES")
Else
    Jour_Ouvré = ""
End If
End Function

pour déterminer quelles valeurs prendre dans les plages nommées, on utilise le N° de ligne où se trouve la fonction.
Ici pour G2 ( donc ligne 2) on choisira la ligne (2 -1 soit 1) des plages nommées.

à adapter en fonction de la position des données

A+

Edit :
Juste après : Function Jour_Ouvré()
Rajouter : Application.Volatile

 

[Paf]

re,

une autre version plus simple à utiliser:

en G2 (ou autre) : =Jour_Ouvré_V2(A1) à tirer vers le bas

et dans un module standard :

Function Jour_Ouvré_V2(Position As Range)
Application.Volatile
lig = Position.Row
Rec = Range("DateReception")(lig)
Rep = Range("DateReparation")(lig)
If Range("test")(lig) = "oui" Then
    Jour_Ouvré_V2 = Evaluate("NB.JOURS.OUVRES(""" & Rec & """,""" & Rep & """,JOURSFERIES")
Else
    Jour_Ouvré_V2 = ""
End If
End Function

le fait de rajouter le paramètre A1, permet, en récupérant le N° de ligne de cette adresse, de définir le rang à utiliser dans les plages nommées. rien à voir avec le contenu de la cellule A1.
Le fait de tirer vers le bas cette formule fait passer A1 en A2,A3,A4,.... on a donc directement la ligne à utiliser pour les plages nommées.
Il faudra toujours utiliser A1 pour la première formule, même si elle se trouve en W15.

On pourrait utiliser B1 ou C1 ...., c'est le 1 qui importe.

A+

 

[Bebere]

bonsoir Profane,Paf
met une formule à droite de colonne test
dans la formule joursferies donne une erreur,alors mis Ref!A2:A6

Sub calcul()
    Dim RngTest As Range, cel As Range, c1 As Byte, c2 As Byte, c3 As Byte

    c1 = Feuil1.Rows(1).Find("Date de reception", LookIn:=xlValues, lookat:=xlWhole).Column
    c2 = Feuil1.Rows(1).Find("Date de reparation", LookIn:=xlValues, lookat:=xlWhole).Column
    c3 = Feuil1.Rows(1).Find("test", LookIn:=xlValues, lookat:=xlWhole).Column
    Set RngTest = Feuil1.Range(Feuil1.Cells(2, c3), Feuil1.Cells(5, c3))
    For Each cel In RngTest
        Feuil1.Cells(cel.Row, c3 + 1).FormulaLocal = "=si(" & cel.Address(0, 0) & "=" & """Oui""" & ";NB.Jours.ouvres(" & Cells(cel.Row, c1).Address(0, 0) & ";" & Cells(cel.Row, c2).Address(0, 0) & ";Ref!A2:A6);0)"
    Next

End Sub

 

[Profane]

Bonjour à tous
et merci d'avoir pris le temps de me répondre
@Paf : le code est erroné, car je n'obtiens qu'une erreur en G2 ou autre, dommage l'idee de faire appel à une fonction
était séduisant et novateur pour moi ;-)
@Bebere, ca fonctionne super !, j ai juste remplacé la référence joursferies, en majuscule et ca marche
encore un grand merci
@+

 

[Paf]

Re,

@Paf : le code est erroné, car je n'obtiens qu'une erreur en G2 ou autre

Curieux !! ça fonctionne sur le classeur fourni !!
Est ce le code ?
La fonction est-elle bien copiée dans un module standard ?
votre classeur de test est-il le même que le classeur fourni ?

A+

 

[Bebere]

Paf aussi testé avec NB.JOURS.OUVRES erreur #nom?
changé par NETWORKDAYS
ligne 2 erreur #valeur!
ligne 3 215
ligne 4 vide normal test=non
ligne 5 -41

 

[Paf]

Re,

Sur XL 2003, avec NB.JOURS.OUVRES ou NETWORKDAYS

ligne 2 : 32
ligne 3 : 9
ligne 4 vide normal test=non
ligne 5 : 63

Les versions Excel plus récentes seraient susceptibles ???

Je ne vois pas d'où pourrait venir l'erreur.

A+

 

[Bebere]

Paf testé sous 2010
après testé comme suit
résultats
32
vide
-158
vide

Function Jour_Ouvré_V2(Position As Range)
Dim Lig As Long, Rec As Date, Rep As Date, x As String, y As String ', z As String
Application.Volatile
Lig = Position.Row
x = "A" & Lig: y = "B" & Lig

'Rec = Range("DateReception")(Lig) 'CDate(Format(, "dd/mm/yyyy"))
'Rep = Range("DateReparation")(Lig) 'CDate(Format(, "dd/mm/yyyy"))
'z = "NB.JOURS.OUVRES(" & x & ";" & y & ";JOURSFERIES"
If Range("test")(Lig) = "oui" Then
'    Jour_Ouvré_V2 = Evaluate("NB.JOURS.OUVRES(" & x & ";" & y & ";JOURSFERIES") 'NB.JOURS.OUVRES
    Jour_Ouvré_V2 = Evaluate("NETWORKDAYS(" & x & "," & y & ",JOURSFERIES") 'NB.JOURS.OUVRES
'    Jour_Ouvré_V2 = Evaluate("NETWORKDAYS(""" & Rec & """,""" & Rep & """,JOURSFERIES") 'NB.JOURS.OUVRES
Else
    Jour_Ouvré_V2 = ""
End If
End Function

 

[Paf]

re,

avec cette dernière fonction chez moi les résultats sont, avec en D2 : =Jour_Ouvré_V2(A1):
#VALEUR!
32
vide
-158

Ce qui semble normal, puisqu'alors pour la ligne 2, les dates passées à NB.JOURS.OUVRES ou NETWORKDAYS sont x (= "A1") pour la première et y ( = "B1" ) ; donc un décalage de 1 ligne .

pour supprimer le décalage il faudrait en D2 : =Jour_Ouvré_V2(A2) ( et on obtient le résultat du post 11) mais si les lignes de dates sont bonnes, c'est la ligne de test qui est décalée.

De plus avec cette méthode on perd l'intérêt de s'adresser directement aux plages nommées et ainsi de s'affranchir de la position des colonnes.


Après mûres ( peut-être !) réflexions, ce qui gênerait c'est de passer directement les dates à NB.JOURS.OUVRES ou NETWORKDAYS.

Une version, qui passe les adresses plutôt que les valeurs, qui fonctionne chez moi aussi bien avec NB.JOURS.OUVRES ou NETWORKDAYS, et conserve l'utilisation des plages nommées :

Function Jour_Ouvré_V3(Position As Range)
Dim Lig As Long, Rec As String, Rep As String
Application.Volatile
Lig = Position.Row
Rec = Range("DateReception")(Lig).Address
Rep = Range("DateReparation")(Lig).Address
If Range("test")(Lig) = "oui" Then
     ' Jour_Ouvré_V2 = Evaluate("NB.JOURS.OUVRES(" & Rec & "," & Rep & ",JOURSFERIES") 
    Jour_Ouvré_V3 = Evaluate("NETWORKDAYS(" & Rec & "," & Rep & ",JOURSFERIES") 'NB.JOURS.OUVRES
Else
    Jour_Ouvré_V3 = ""
End If
End Function

A+

 

[Bebere]

Bonjour
Bien Paf la dernière version, c'est la bonne

 

[Paf]

re,

quand même curieux que la version V2 ne fonctionne que sous XL 2003 ?

Bonne journée

 

[Profane]

Bonjour à vous
merci d'avoir continué à creuser le sujet
Cordialement