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

Profane

XLDnaute Occasionnel
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
 

Paf

XLDnaute Barbatruc
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

XLDnaute Barbatruc
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

XLDnaute Barbatruc
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

XLDnaute Occasionnel
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

XLDnaute Barbatruc
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

XLDnaute Barbatruc
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

XLDnaute Barbatruc
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+
 

Statistiques des forums

Discussions
300 907
Messages
1 988 363
Membres
210 125
dernier inscrit
manager2015