Microsoft 365 Fonction vba avec index

[JPAd]

Bonjour,

J'ai réalisé une formule dans vba venant chercher automatiquement dans diffèrent index les prix et la mains d'oeuvre pour l'additionner et donner la valeur total.
La macro renvoie la valeur #valeur.
Je comprend pas ou ce trouve l'erreur.

Merci

 

[vgendron]

il suffit dans ce cas la de modifier l'appel de la fonction
(PS: j'étais passé à une range complète, car bizarrement, je n'arrivais pas à suivre la fonction en mode débug...

Public Function CalculPrixTotal(Station As String, NbPoste As String, DNP As Integer, DNS As Integer, DNC As Integer, DNT As Integer, TypeRetour As Integer) As Long
'TypeRetour: 1 = Prix / 2= Main d'oeuvre ==> selon le type retour, la fonction Prix va chercher le résultat dans la colonne idoine

   Dim DNA As Integer 'à supprimer ici, et intégrer dans la liste des paramètres d'entrée
    
    Dim cible As String
    Dim Vanne As String
    Dim Qté As Integer
    Dim Total As Long
    
    'on extrait les critères de la zone critères passée en paramètre
'    Station = ZoneCrit.Cells(1, 1)
'    NbPoste = ZoneCrit.Cells(2, 1)
'    DNP = ZoneCrit.Cells(3, 1)
'    DNS = ZoneCrit.Cells(4, 1)
'    DNC = ZoneCrit.Cells(5, 1)
'    DNT = ZoneCrit.Cells(6, 1)
    DNA = 0 'à définir pour les accessoires
    
    
    With Sheets("Station").ListObjects("t_Station") 'avec la table "t_Station" de la feuille "Station"
        For i = 1 To .ListRows.Count 'pour chaque ligne de la table
            If .ListColumns("Station").DataBodyRange(i) = Station And .ListColumns("Nb de postes").DataBodyRange(i) = NbPoste Then 'si on a la station et le bon nombre de postes
                For SPF = 1 To 6 ' Calcul stations primaires froids (1 à 6) 'on parcourt les 6 stations primaires froid
                    cible = .ListColumns("Station primaire froid " & SPF).DataBodyRange(i) 'on récupère le contenu dans la Table
                    If cible <> "" Then 'si non vide
                        Qté = CInt(Replace(Replace(Split(cible, "]")(0), "[", ""), "x", "")) 'on extrait la qté
                        Vanne = Split(cible, "] ")(1) 'on extrait la Vanne
                        Total = Total + Prix(Vanne, DNP, TypeRetour) * Qté 'on appelle la function Prix avec le bon DN pour mettre à jour le total
                    End If
                Next SPF
                'idem sur les autres colonnes
                For SPF = 1 To 3 ' Calcul stations secondaire froids (1 à 3)
                    cible = .ListColumns("Station secondaire froid " & SPF).DataBodyRange(i)
                    If cible <> "" Then
                        Qté = CInt(Replace(Replace(Split(cible, "]")(0), "[", ""), "x", ""))
                        Vanne = Split(cible, "] ")(1)
                        Total = Total + Prix(Vanne, DNS, TypeRetour) * Qté
                    End If
                Next SPF
                
                
                For SPF = 1 To 6 ' Calcul Chaud (1 à 6)
                    cible = .ListColumns("Station chaud " & SPF).DataBodyRange(i)
                    If cible <> "" Then
                        Qté = CInt(Replace(Replace(Split(cible, "]")(0), "[", ""), "x", ""))
                        Vanne = Split(cible, "] ")(1)
                        Total = Total + Prix(Vanne, DNC, TypeRetour) * Qté
                    End If
                Next SPF
                                
                For SPF = 1 To 2 ' Calcul Station terminale forid (1 à 2)
                    cible = .ListColumns("Vanne terminale " & SPF & " sur bat").DataBodyRange(i)
                    If cible <> "" Then
                        Qté = CInt(Replace(Replace(Split(cible, "]")(0), "[", ""), "x", ""))
                        Vanne = Split(cible, "] ")(1)
                        Total = Total + Prix(Vanne, DNT, TypeRetour) * Qté
                    End If
                Next SPF
                
                For SPF = 1 To 2 ' Calcul Station terminale forid (1 à 2)
                    cible = .ListColumns("Accessoire " & SPF).DataBodyRange(i)
                    If cible <> "" Then
                        Qté = CInt(Replace(Replace(Split(cible, "]")(0), "[", ""), "x", ""))
                        Vanne = Split(cible, "] ")(1)
                        Total = Total + Prix(Vanne, DNA, TypeRetour) * Qté
                    End If
                Next SPF
            End If
        Next i

    End With
    CalculPrixTotal = Total
End Function

 

[JPAd]

J'ai encore deux questions.
Le calcul marche bien par contre l'accessoire 2 n'est pas pris en compte dans le calcul. Jai supprimé DNA qui n'a pas d'utilité. Les accessoires n'ont qu'une seul référence pas comme les vannes qui dépendent des DN.
Egalement j'arrive pas à faire fonctionner la macro sur une autre feuille qui ferait appelle à la base de donnée implanté dans ce fichier test. Je pense que le problème doit venir des références du gestionnaire de noms.

 

[JPAd]

Avec le fichier

 

[vgendron]

dans la table "Vannes", en face des accessoires, il faut mettre 0 dans la colonne DN

tu as modifié la fonction d'appel en ajoutant un paramètre "Quantité de postes" ==> mais tu n'en fais rien.. à quoi ca sert?
ha si pardon. je n'avais pas vu la modif dans l'avant dernière boucle

 

[vgendron]

pour l'utilisation dans une autre feuille.. pas compris
et ce que tu n'as pas vu, c'est que la fonction vba n'utilise pas une seule de tes plages nommées., seulement le nom des colonnes des TS (Tables Structurées)
et tu n'as donc pas vu que je les ai toutes supprimées..

 

[JPAd]

SI j'ai bien vu que tu avais supprimé toute les plages nommées pour en recréer avec des tables structurés.
Tout est beaucoup plus simple d'ailleurs
Je voudrais que l'onglet calcul soit dans une autre feuille.
Jai besoin de cette macro dans plusieurs feuille différentes et il faudrait qu'elle puisse interrogé la feuille test pur récupérer les infos des onglets stations et de l'onglet vanne. Normalement cela marche de faire référence dans le gestionnaire de noms à une autre feuille sauf que la je n'y arrive pas. Jamais utilisé auparavant des tables structurées.

 

[TooFatBoy]

Jai besoin de cette macro dans plusieurs feuille différentes et il faudrait qu'elle puisse interrogé la feuille test pur récupérer les infos des onglets stations et de l'onglet vanne. Normalement cela marche de faire référence dans le gestionnaire de noms à une autre feuille sauf que la je n'y arrive pas. Jamais utilisé auparavant des tables structurées.

Si tu utilises des TS tu n'as pas besoin de faire référence à la feuille qui les contient : ça fonctionne où que soit le TS.

 

[vgendron]

SI j'ai bien vu que tu avais supprimé toute les plages nommées pour en recréer avec des tables structurés.
Tout est beaucoup plus simple d'ailleurs
Je voudrais que l'onglet calcul soit dans une autre feuille.
Jai besoin de cette macro dans plusieurs feuille différentes et il faudrait qu'elle puisse interrogé la feuille test pur récupérer les infos des onglets stations et de l'onglet vanne. Normalement cela marche de faire référence dans le gestionnaire de noms à une autre feuille sauf que la je n'y arrive pas. Jamais utilisé auparavant des tables structurées.

Je ne comprend pas bien ce qui te gêne..
la fonction perso "=calculprix(....) peut etre utilisée n'importe ou dans le classeur..
et tu peux la mettre plusieurs fois en plusieurs endroits différents.. tout comme n'importe quelle fonction Excel native (c'est pour ca qu'on appelle ca "Fonction" personalisée

peux tu poster un fichier avec l'exemple qui te pose souci..

 

[JPAd]

J'ais pas été bien clair dans mes explications.
En effet la macro marche bien dans n'importe quel feuille du classeur.
Mais je cherche a avoir deux classeurs différent. Un classeur avec la base de donnée et un autre avec la feuille de calcul.
Le problème étant que la feuille de calcul avec la macro n'arrive pas à faire le lien avec le gestionnaire de noms et les tableau structurés du classeur base de donnée.

 

[vgendron]

Dans la macro, il faut modifier le code pour
1) définir le classeur "Base de donnée"
2) faire travailler la macro sur cette BDD

et vu que tu vas vouloir après que ca fonctionne sans avoir à ouvrir le classeur BDD (bah oui tant qu'à faire. autant donner les infos au fur et à mesure), et bah la;. il va falloir tout revoir et se tourner vers PowerQuery .. et la.. je ne maitrise pas du tout..

 

[JPAd]

Non pas de soucis de ce côté la le classeur base de donnée sera ouvert. Toute mes macros fonctionne avec le classeur base de donnée ouvert.

 

[vgendron]

dans ce cas
il suffit de modifier le code comme indiqué précédemment

Dim WbSource as workbook
DIm TSBDD as listobject

set WbSource=workbooks("NomDuClasseur")
set TSBDD=wsbSource.sheets("NomFeuille").listobjects("NomTable")

et ensuite dans le code
with TSBDD
for i= 1 to....

 

[JPAd]

Bonsoir,

J'ai essayé de modifier le code en intégrant les nouveaux éléments. Mais la macro n'arrive pas à récupérer les infos des tables structuré situé dans l'autre classeur. Même ci l'autre classeur est ouvert.
Je vois pas trop ou ce situe l'erreur.

 

[vgendron]

Hello

tu as des erreurs de syntaxe dans la variable WbSource (tu as ecrit WbsSource)
et dans le nom de la table

Remplace ton code par celui-ci

Public Function CalculPrixTotal(Station As String, NbPoste As String, DNP As Integer, DNS As Integer, DNC As Integer, DNT As Integer, QuaPoste As Integer, TypeRetour As Integer) As Long
'TypeRetour: 1 = Prix / 2= Main d'oeuvre ==> selon le type retour, la fonction Prix va chercher le résultat dans la colonne idoine

    Dim WbSource As Workbook
    Dim TSBDD As ListObject
    Dim cible As String
    Dim Vanne As String
    Dim Qté As Integer
    Dim Total As Long

    Set WbSource = Workbooks("Base de donnée.xlsm")
    Set TSBDD = WbSource.Sheets("Station").ListObjects("t_Station")

    With TSBDD  '  With Sheets("Station").ListObjects("t_Station") 'avec la table "t_Station" de la feuille "Station"
        For i = 1 To .ListRows.Count   '     For i = 1 To .ListRows.Count 'pour chaque ligne de la table
            If .ListColumns("Station").DataBodyRange(i) = Station And .ListColumns("Nb de postes").DataBodyRange(i) = NbPoste Then 'si on a la station et le bon nombre de postes
                For SPF = 1 To 6 ' Calcul stations primaire froid (1 à 6) 'on parcourt les 6 stations primaires froid
                    cible = .ListColumns("Station primaire froid " & SPF).DataBodyRange(i) 'on récupère le contenu dans la Table
                    If cible <> "" Then 'si non vide
                        Qté = CInt(Replace(Replace(Split(cible, "]")(0), "[", ""), "x", "")) 'on extrait la qté
                        Vanne = Split(cible, "] ")(1) 'on extrait la Vanne
                        Total = Total + Prix(Vanne, DNP, TypeRetour) * Qté 'on appelle la function Prix avec le bon DN pour mettre à jour le total
                    End If
                Next SPF
                'idem sur les autres colonnes
                For SPF = 1 To 3 ' Calcul stations secondaire froid (1 à 3)
                    cible = .ListColumns("Station secondaire froid " & SPF).DataBodyRange(i)
                    If cible <> "" Then
                        Qté = CInt(Replace(Replace(Split(cible, "]")(0), "[", ""), "x", ""))
                        Vanne = Split(cible, "] ")(1)
                        Total = Total + Prix(Vanne, DNS, TypeRetour) * Qté
                    End If
                Next SPF
                                
                For SPF = 1 To 6 ' Calcul Station Chaud (1 à 6)
                    cible = .ListColumns("Station chaud " & SPF).DataBodyRange(i)
                    If cible <> "" Then
                        Qté = CInt(Replace(Replace(Split(cible, "]")(0), "[", ""), "x", ""))
                        Vanne = Split(cible, "] ")(1)
                        Total = Total + Prix(Vanne, DNC, TypeRetour) * Qté
                    End If
                Next SPF
                                
                For SPF = 1 To 2 ' Calcul Station terminale froid (1 à 2)
                    cible = .ListColumns("Vanne terminale " & SPF & " sur bat").DataBodyRange(i)
                    If cible <> "" Then
                        Qté = QuaPoste
                        Vanne = Split(cible, "] ")(1)
                        Total = Total + Prix(Vanne, DNT, TypeRetour) * Qté
                    End If
                Next SPF
                
                For SPF = 1 To 2 ' Calcul Accessoire station (1 à 2)
                    cible = .ListColumns("Accessoire " & SPF).DataBodyRange(i)
                    If cible <> "" Then
                        Qté = CInt(Replace(Replace(Split(cible, "]")(0), "[", ""), "x", ""))
                        Vanne = Split(cible, "] ")(1)
                        Total = Total + Prix(Vanne, 0, TypeRetour) * Qté
                    End If
                Next SPF
            End If
        Next i
    End With
    CalculPrixTotal = Total
End Function


Function Prix(cible As String, DN As Integer, TypeRetour As Integer) As Long
Dim WbSource As Workbook
Dim TSPrix As ListObject
    
    Set WbSource = Workbooks("Base de donnée.xlsm")
    Set TSPrix = WbSource.Sheets("Vannes").ListObjects("t_PrixVannes")
    
        Prix = 0 'initialisation à 0
        NomCol = IIf(TypeRetour = 1, "Prix", "Mains d'œuvre")
        
    With TSPrix
        For i = 1 To .ListRows.Count 'on parcourt la table, ligne à ligne
            If .ListColumns("Vanne").DataBodyRange(i) = cible And .ListColumns("DN").DataBodyRange(i) = DN Then 'on cherche la ligne avec la cible et le bon DN
                Prix = .ListColumns(NomCol).DataBodyRange(i) 'on récupère le prix
                Exit Function   'on quitte la fonction
            End If
        Next i
    End With
End Function

 

[JPAd]

Merci ça marche nickel maintenant