Ceci est une page optimisée pour les mobiles. Cliquez sur ce texte pour afficher la vraie page.

Microsoft 365 Fonction vba avec index

Créer une discussion
  • Initiateur de la discussion Initiateur de la discussion JPAd
  • Date de début Date de début

Boostez vos compétences Excel avec notre communauté !

Rejoignez Excel Downloads, le rendez-vous des passionnés où l'entraide fait la force. Apprenez, échangez, progressez – et tout ça gratuitement ! 👉 Inscrivez-vous maintenant !

S'inscrire
J

JPAd

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

Pièces jointes

Trier par date Trier par votes
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...

VB: 
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
 
Vote positif 0 Vote négatif
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.
 
Vote positif 0 Vote négatif
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
 
Vote positif 0 Vote négatif
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..
 
Vote positif 0 Vote négatif
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.
 
Vote positif 0 Vote négatif
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.
 
Vote positif 0 Vote négatif
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..
 
Vote positif 0 Vote négatif
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.
 

Pièces jointes

Vote positif 0 Vote négatif
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..
 
Vote positif 0 Vote négatif
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....
 
Vote positif 0 Vote négatif
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.
 

Pièces jointes

Vote positif 0 Vote négatif
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
VB: 
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
 
Vote positif 0 Vote négatif
Identifiez-vous ou inscrivez-vous pour participer.
Rejoins nos membres "Supporter XLD" et booste ton expérience Excel !
- Navigue sans publicité
- Accède à Cléa, notre assistante IA experte Excel... et pas que...
- Profite de fonctionnalités exclusives
Ton soutien permet à Excel Downloads de rester 100% gratuit et de continuer à rassembler les passionnés d'Excel.
Je deviens Supporter XLD

Discussions similaires

D
  • Résolu(e)
XL 2021 Recherche valeurs dates avec index
Réponses
5
Affichages
49
Did25
D
T
  • Question Question
XL 2016 INDEX EQUV RECHERCHEV sur une partie du nom
Réponses
12
Affichages
196
Titiem
T
E
  • Question Question
Power Query Index TP de l'INSEE - Ajuster une formule à chaque mise à jour d'un tableau généré avec PQ
2
Réponses
21
Affichages
976
laurent950
E
  • Résolu(e)
XL 2019 index equiv avec decalage
Réponses
4
Affichages
636
escudo
E
G
  • Question Question
XL 2016 Formule INDEX EQUIV retourne #Ref
Réponses
3
Affichages
62
gaelle56
G
M
  • Résolu(e)
XL pour MAC Index Equiv, Correction informatisée tests
Réponses
2
Affichages
53
MaiBZH
M
S
  • Résolu(e)
XL 2019 formule recherche qui bug
Réponses
4
Affichages
113
Sylvie J
S
C
  • Question Question
XL 2013 rechercher la dernière date
Réponses
4
Affichages
169
candido57
C
M
  • Question Question
XL 2019 Fonction Moyenne(Decaler...) avec Index et Equiv
Réponses
2
Affichages
151
Maya3375
M
M
  • Résolu(e)
XL 2021 copier des cellules
Réponses
9
Affichages
177
Phil69970
S
  • Résolu(e)
Microsoft 365 Problème de référence à une Feuil de calcul avec .FormulaLocal
Réponses
6
Affichages
112
sylvanu
C
  • Résolu(e)
XL 2013 Renvoyer 2 dans la cellule
Réponses
2
Affichages
187
candido57
C
M
  • Question Question
XL 2021 mise a jour inter classeur avec autorisation
Réponses
1
Affichages
211
wDog66
R
  • Question Question
Microsoft 365 formule sommeprod avec index -Equi
Réponses
7
Affichages
486
radia
R
  • Résolu(e)
Microsoft 365 Recherche avec plusieurs critères
Réponses
2
Affichages
333
Electron24
S
  • Question Question
XL 2016 Comment classer des données suivant des conditions multiples?
Réponses
8
Affichages
363
surchat
B
  • Question Question
XL 2016 VBA et valeur cible sur cellules différentes
Réponses
3
Affichages
260
Lolote83
L
B
  • Question Question
XL 2019 PB avec fonction rechercheV ou recherche H
Réponses
5
Affichages
110
bruiz
B
S
  • Question Question
XL 2016 Comment attribuer des classes avec trois conditions?
Réponses
7
Affichages
100
TooFatBoy
J
  • Question Question
XL 2010 définir un nom de cellule en fonction de la valeur d'une autre
Réponses
12
Affichages
902
jojo4738
J
Les cookies sont requis pour utiliser ce site. Vous devez les accepter pour continuer à utiliser le site. En savoir plus…