Fonction INDEX + EQUIV sous VBA

Version imprimable

08/07/2015, 14h13
ti_mouton

1 pièce(s) jointe(s)

Fonction INDEX + EQUIV sous VBA

Bonjour à tous,

J'ai besoin d'utiliser les formules excel INDEX et EQUIV dans une macro, simplement je ne sais pas comment m'y prendre. Ma base de travail se présente comme suit : j'ai un dossier nommé "Suivi des temps Maintenance" dans lequel j'ai un classeur "Base de Données", un classeur "ClasseurSaisie", et un classeur "Matrice Frais (Km+MO)". Le classeur de saisie est munie d'une macro qui a été développée par des membres du forum CCM et des membres de ce forum Développez (que je remercie grandement). J'ai besoin de rajouter un code qui permette de faire des calculs en récupérant des données dans le classeur "Matrice Frais (Km+MO). Dans les colonnes R - S - T - U - V - W du classeur saisie, j'ai écrit les formules excel qu'il faudrait pouvoir retranscrire sous VBA.

Je vous met l'ensemble du dossier à disposition (avec les 3 classeurs inclus), ça sera surement plus simple à comprendre qu'une longue explication.

Merci pour votre aide
08/07/2015, 14h48
parmi

Bonjour,

Sélectionne la cellule avec une formule et mets une petite macro avec seulement

Code:

MsgBox Activecell.Formula

Ça te retournera la formule que tu pourras utiliser avec

Code:

Range("mon range").Formula = "......"

Le range peut être une plage si nécessaire.
Il suffit de faire attention aux signes de $ à placer aux bons endroits lorsque nécessaire.
08/07/2015, 15h22
ti_mouton

Merci pour cette astuce Parmi.

J'ai cependant une petite question supplémentaire : y a-t-il un moyen de copier/coller le contenu du MsgBox (la formule est très longue ...) ?
08/07/2015, 15h31
Marc-L

Bonjour,

utiliser alors Debug.Print et copier la formule depuis la fenêtre Exécution …

_____________________________________________________________________________________________________
Je suis Charlie, Bardo, Sousse
08/07/2015, 15h38
ti_mouton

Bonjour Marc-L,

J'ai remplacé MsgBox par Debug.Print comme vous me le suggérez, par contre je ne comprend pas la deuxième étape (je ne suis pas très callée en VBA). Je suis allée dans la fenetre Execution mais que dois je faire ensuite ?
08/07/2015, 15h39
parmi

Tape Ctrl-G pour faire apparaître la fenêtre Exécution.
08/07/2015, 15h42
Marc-L

Après avoir exécuté le code, la formule de la cellule active apparait dans cette fenêtre …

Génial Merci beaucoup !

Grace à votre aide je sais maintenant comment récuperer la formule sous VBA, je voudrais maintenant pouvoir intégrer la formule à la macro de mon userform. La formule doit s'appliquer à la colonne R.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
Private Sub CommandButton1_Click()
Dim DerLig As Integer
If TextBox2 = "" Then
    MsgBox "Merci de remplir le champ Déscription"
    Exit Sub
End If
With Worksheets("Saisie")
    If Ligne_Modif = 0 Then
        DerLig = .Range("B" & Rows.Count).End(xlUp).Row + 1
    Else
        DerLig = Ligne_Modif
    End If
    .Range("C" & DerLig).Value = ComboBox1.Value
    .Range("D" & DerLig).Value = ComboBox2.Value
    .Range("E" & DerLig).Value = ComboBox3.Value
    .Range("F" & DerLig).Value = ComboBox4.Value
    .Range("B" & DerLig).Value = ComboBox5.Value
    .Range("G" & DerLig).Value = TextBox1
    .Range("G" & DerLig).NumberFormat = "dd/mm/yyyy"
    .Range("H" & DerLig).Value = TextBox2.Value
    .Range("I" & DerLig).Value = TextBox4.Value
    .Range("J" & DerLig).Value = TextBox5.Value
    .Range("K" & DerLig).Value = TextBox3.Value
    .Range("L" & DerLig) = semaine(CDate(TextBox1))
    .Range("M" & DerLig) = Format(CDate(TextBox1), "mmmm")
    .Range("N" & DerLig) = Year(CDate(TextBox1))
    .Range("O" & DerLig).Value = OptionButton1.Value
    .Range("P" & DerLig).Value = ComboBox6.Value
    .Range("Q" & DerLig).Value = ComboBox7.Value
    .Range("R" & DerLig).Formula = IF(O:O=TRUE,(INDEX('\\BRUTUS\Bureautique\AssistantGestion\BETON\CDG FVMB VMBS\Florence GASCON\Essai VBA MAppy\Suivi des temps Maintenance - Copie\[Matrice Frais (Km + MO).xlsm]Matrice Distance'!$A:$BZ,MATCH($C:$C,'\\BRUTUS\Bureautique\AssistantGestion\BETON\CDG FVMB VMBS\Florence GASCON\Essai VBA MAppy\Suivi des temps Maintenance - Copie\[Matrice Frais (Km + MO).xlsm]Matrice Distance'!$A:$A,0),MATCH($D:$D,'\\BRUTUS\Bureautique\AssistantGestion\BETON\CDG FVMB VMBS\Florence GASCON\Essai VBA MAppy\Suivi des temps Maintenance - Copie\[Matrice Frais (Km + MO).xlsm]Matrice Distance'!$1:$1,0)))*2,INDEX('\\BRUTUS\Bureautique\AssistantGestion\BETON\CDG FVMB VMBS\Florence GASCON\Essai VBA MAppy\Suivi des temps Maintenance - Copie\[Matrice Frais (Km + MO).xlsm]Matrice Distance'!$A:$BZ,MATCH($C:$C,'\\BRUTUS\Bureautique\AssistantGestion\BETON\CDG FVMB VMBS\Florence GASCON\Essai VBA MAppy\Suivi des temps Maintenance - Copie\[Matrice Frais (Km + MO).xlsm]Matrice Distance'!$A:$A,0),MATCH($D:$D,'\\BRUTUS\Bureautique\Assista
ntGestion\BETON\CDG FVMB VMBS\Florence GASCON\Essai VBA MAppy\Suivi des temps Maintenance - Copie\[Matrice Frais (Km + MO).xlsm]Matrice Distance'!$1:$1,0)))
End With
 
Unload UserForm1
 
End Sub

Evidemment je ne suis pas très douée donc cela ne fonctionne pas ... De plus dans la formule, je voudrais que le chemin vers le fichier "Matrice Frais (Km + MO)" soit identifié comme Thisworkbookpath, car le dossier peut être amené changer d'emplacement.

Quelqu'un saurait il m'aidé ?

Merci

08/07/2015, 16h30
Philippe Tulliez

Bonjour,
La lecture de ce billet Ecrire une formule dans Excel à l'aide d'une procédure VBA et la suite éventuellement pour les formules plus complexes Ecrire une formule dans Excel à l'aide d'une procédure VBA (Part 2), t'apportera peut-être une réponse.
09/07/2015, 09h13
ti_mouton

Merci Philippe, cela m'éclaire un peu plus! Cependant je suis toujours bloquée pour transformer le chemin d'accès au classeur "Matrice Frais (Km + MO)" en Thiswoorkbook.Path et l'intégrer dans la formule ... Si quelqu'un a une solution ?

Merci beauucoup
09/07/2015, 10h04
Philippe Tulliez

Bonjour,

Citation:

Cependant je suis toujours bloquée pour transformer le chemin d'accès au classeur "Matrice Frais (Km + MO)" en Thiswoorkbook.Path et l'intégrer dans la formule ...

Si tu as bien lu le deuxième billet, la technique à utiliser est expliquée. Prends le temps de faire des tests.
09/07/2015, 10h18
ti_mouton

Je suis désolée mais je ne vois pas, les exemples qui sont développés dans le billet sont uniquement sur des formules intégrant des éléments qui se trouve dans le même workbook et non pas dans un autre classeur faisant parti du même dossier. Pouvez vous m'éclairer ?

Bonjour,
La propriété FullName de l'objet Workbook te donne le nom et chemin complet du classeur. Que ce soit ThisWorkbook ou Workbooks("LeClsseur")
Si c'est un objet Range que tu utilises la propriété Address avec True comme valeur de l'argument External te donnera le chemin complet du Range.
Exemple
Code:

1 2 3 4 5 6 7 Sub Test() Dim rng As Range, wkb As Workbook Set wkb = Workbooks("FactDataBase.xls") Set rng = wkb.Worksheets("clients").Range("A1").CurrentRegion Debug.Print wkb.FullName Debug.Print rng.Address(external:=True) End Sub
Ces deux propriétés renvoyant une chaîne de caractères, il te suffit de suivre les exemples que j'ai mis sur le billet pour faire un Replace de la balise prévue pour cette adresse par la chaîne de caractères.

Merci pour votre aide Philippe,

J'ai essayé votre code mais un message d'erreur suivant survient : L'indice n'appartient pas à la sélection. Le débogage me surligne la ligne de code Set wkb = ...
Code:

1 2 3 4 5 6 7 Sub Chemin() Dim rng As Range, wkb As Workbook Set wkb = Workbooks("Matrice Frais (Km + MO).xlsm") Set rng = wkb.Worksheets("Matrice Distance").Range("A:A").CurrentRegion Debug.Print wkb.FullName Debug.Print rng.Address(external:=True) End Sub
je ne comprends pas pourquoi ... ?

09/07/2015, 11h17
Philippe Tulliez

Bonjour,
Pourquoi à la ligne 4 de ta procédure fais-tu référence à la colonne A et pas à la cellule A1 ?
Le message d"erreur renvoyé signifie que le classeur ou la feuille n'existe pas (pour le classeur ne serait pas ouvert)

J'ai résolu le pb, j'avais fait une faute de frappe. J'arrive bien à récupérer le chemin du classeur. Simplement je n'arrive pas à intégrer ma formule dans le code de mon userform. Lorsque je copie/colle la formule qui s'affiche dans ma fenetre d'execution, tout s'affiche en rouge et je ne peux rien modifier.
J'ai essayer de reprendre votre ligne

Code:

Const myFormula As String = "=...."

mais je n'arrive pas à y intégrer ma formule suivante

Code:

1
2
=IF(O:O=TRUE,(INDEX('\\BRUTUS\Bureautique\AssistantGestion\BETON\CDG FVMB VMBS\Florence GASCON\Essai VBA MAppy\Suivi des temps Maintenance - Copie\[Matrice Frais (Km + MO).xlsm]Matrice Distance'!$A:$BZ,MATCH($C:$C,'\\BRUTUS\Bureautique\AssistantGestion\BETON\CDG FVMB VMBS\Florence GASCON\Essai VBA MAppy\Suivi des temps Maintenance - Copie\[Matrice Frais (Km + MO).xlsm]Matrice Distance'!$A:$A,0),MATCH($D:$D,'\\BRUTUS\Bureautique\AssistantGestion\BETON\CDG FVMB VMBS\Florence GASCON\Essai VBA MAppy\Suivi des temps Maintenance - Copie\[Matrice Frais (Km + MO).xlsm]Matrice Distance'!$1:$1,0)))*2,INDEX('\\BRUTUS\Bureautique\AssistantGestion\BETON\CDG FVMB VMBS\Florence GASCON\Essai VBA MAppy\Suivi des temps Maintenance - Copie\[Matrice Frais (Km + MO).xlsm]Matrice Distance'!$A:$BZ,MATCH($C:$C,'\\BRUTUS\Bureautique\AssistantGestion\BETON\CDG FVMB VMBS\Florence GASCON\Essai VBA MAppy\Suivi des temps Maintenance - Copie\[Matrice Frais (Km + MO).xlsm]Matrice Distance'!$A:$A,0),MATCH($D:$D,'\\BRUTUS\Bureautique\Assista
ntGestion\BETON\CDG FVMB VMBS\Florence GASCON\Essai VBA MAppy\Suivi des temps Maintenance - Copie\[Matrice Frais (Km + MO).xlsm]Matrice Distance'!$1:$1,0)))

Je ne m'en sors pas...

La chaîne est trop longue pour VBA.

Regarde comment tu pourrais faire

Code:

1
2
3
4
5
    Dim Chemin As String
    Dim MyFormula As String
 
    Chemin = "\\BRUTUS\Bureautique\AssistantGestion\BETON\CDG FVMB VMBS\Florence GASCON\Essai VBA MAppy\Suivi des temps Maintenance - Copie\[Matrice Frais (Km + MO).xlsm]Matrice Distance"
    MyFormula = "=IF(O:O=TRUE,(INDEX('" & Chemin & "'!$A:$BZ,MATCH($C:$C,'" & Chemin & "'!$A:$A,0),MATCH($D:$D,'" & Chemin & "'!$1:$1,0)))*2,INDEX('" & Chemin & "'!$A:$BZ,MATCH($C:$C,'" & Chemin & "'!$A:$A,0),MATCH($D:$D,'" & Chemin & "'!$1:$1,0)))"

09/07/2015, 15h38
Philippe Tulliez

Bonjour,
Comme expliqué dans mon deuxième billet, il faut prévoir des balises dans la chaîne de caractères de cette formule pour ensuite effectuer à l'aide de la fonction Replace un remplacement de cette balise par l'adresse de la plage avec ou sans le chemin.
Pour bien comprendre, effectue des tests en dehors de ta procédure en utilisant le débogage au pas à pas (Touche F8).

A titre de curiosité, que testes-tu dans IF(O:O=TRUE
09/07/2015, 18h27
ti_mouton

Si le OptionButton1 de mon userform est coché, alors la valeur "VRAI" sera renseignée dans la colonne O, et si cette valeur VRAI est renseigné alors je lance un calcul.

Citation:
Envoyé par parmi
La chaîne est trop longue pour VBA.

Regarde comment tu pourrais faire
Code:

1 2 3 4 5 Dim Chemin As String Dim MyFormula As String Chemin = "\\BRUTUS\Bureautique\AssistantGestion\BETON\CDG FVMB VMBS\Florence GASCON\Essai VBA MAppy\Suivi des temps Maintenance - Copie\[Matrice Frais (Km + MO).xlsm]Matrice Distance" MyFormula = "=IF(O:O=TRUE,(INDEX('" & Chemin & "'!$A:$BZ,MATCH($C:$C,'" & Chemin & "'!$A:$A,0),MATCH($D:$D,'" & Chemin & "'!$1:$1,0)))*2,INDEX('" & Chemin & "'!$A:$BZ,MATCH($C:$C,'" & Chemin & "'!$A:$A,0),MATCH($D:$D,'" & Chemin & "'!$1:$1,0)))"
Bonjour Parmi,

Merci pour ta réponse.
J'ai repris ton code en rajoutant des éléments.
Code:

1 2 3 4 5 6 7 8 9 10 11 Sub Calcul1() Dim Chemin As String Dim MyFormula As String Dim Source As String Source = ThisWorkbook.Path Chemin = Source & "\[Matrice Frais (Km + MO).xlsm]Matrice Distance" MyFormula = "=IF(O:O=TRUE,(INDEX('" & Chemin & "'!$A:$BZ,MATCH($C:$C,'" & Chemin & "'!$A:$A,0),MATCH($D:$D,'" & Chemin & "'!$1:$1,0)))*2,INDEX('" & Chemin & "'!$A:$BZ,MATCH($C:$C,'" & Chemin & "'!$A:$A,0),MATCH($D:$D,'" & Chemin & "'!$1:$1,0)))" Range("R") = MyFormula End Sub
Ce code fonctionne mais il a fini par faire planter excel... Je pense qu'il faudrait peut être modifier le code pour que la formule ne s'applique que lorsque la colonne O est non vide. De plus je voudrais que la formule ne s'applique qu'à partir de la cellule R2 puis sur toute la colonne R.

Aurais tu une idée pour m'aider ? merci beaucoup

Je n'ai pas testé, mais quelque chose comme ceci devrait fonctionner
Vérifie la formule. J'ai possiblement supprimer les mauvaises parenthèses...
Donc, plutôt que de vérifier dans ta formule s'il y a des données en O, tu le fais avant avec WorksheetFunction

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
Sub Calcul1()
    Dim nbLignes as long
    Dim Chemin As String
    Dim MyFormula As String
    Dim Source As String
 
    nbLignes = Cells(rows.count,"A").End(xlup).row
    Source = ThisWorkbook.Path
    Chemin = Source & "\[Matrice Frais (Km + MO).xlsm]Matrice Distance"
    If Application.Worksheetfunction.counta(columns("O")) > 0 Then
       MyFormula = "=INDEX('" & Chemin & "'!$A:$BZ,MATCH($C:$C,'" & Chemin & "'!$A:$A,0),MATCH($D:$D,'" & Chemin & "'!$1:$1,0)*2)"
       Range("R2:R" & nbLignes) = MyFormula
    End if
End Sub

15/07/2015, 09h18
ti_mouton

Merci beaucoup Parmi !