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

Profane · 7 Décembre 2016

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 · 8 Décembre 2016

bonjour
Profane une pièce jointe serait bienvenue

Profane · 8 Décembre 2016

Bonjour bebere
le fichier exemple ci joint
Cordialement

Paf · 8 Décembre 2016

Bonjour Profane, Bebere

un essai par fonction personnalisée :

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

et dans un module standard:

VB:

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 · 8 Décembre 2016

re,

une autre version plus simple à utiliser:

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

et dans un module standard :

VB:

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 · 8 Décembre 2016

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

Code:

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 · 9 Décembre 2016

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 · 9 Décembre 2016

Re,

Profane à dit:
@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 · 9 Décembre 2016

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 · 9 Décembre 2016

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 · 9 Décembre 2016

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

Code:

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 · 10 Décembre 2016

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 :

VB:

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 · 10 Décembre 2016

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

Paf · 10 Décembre 2016

re,

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

Bonne journée

Profane · 13 Décembre 2016

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

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

Profane

XLDnaute Occasionnel

Bebere

XLDnaute Barbatruc

Profane

XLDnaute Occasionnel

Pièces jointes

Paf

XLDnaute Barbatruc

Paf

XLDnaute Barbatruc

Bebere

XLDnaute Barbatruc

Profane

XLDnaute Occasionnel

Paf

XLDnaute Barbatruc

Bebere

XLDnaute Barbatruc

Paf

XLDnaute Barbatruc

Bebere

XLDnaute Barbatruc

Paf

XLDnaute Barbatruc

Bebere

XLDnaute Barbatruc

Paf

XLDnaute Barbatruc

Profane

XLDnaute Occasionnel

Discussions similaires