Discussion :

[ti_mouton]

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

[parmi]

Bonjour,

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

MsgBox Activecell.Formula

Ça te retournera la formule que tu pourras utiliser avec

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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.

[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 ...) ?

[Marc-L]

Bonjour,

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



_____________________________________________________________________________________________________
Je suis Charlie, Bardo, Sousse

[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 ?

[parmi]

Tape Ctrl-G pour faire apparaître la fenêtre Exécution.

[Marc-L]

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

[ti_mouton]

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

[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.

[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

[Philippe Tulliez]

Bonjour,

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.

[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 ?

[Philippe Tulliez]

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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.

[ti_mouton]

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 ... ?

[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)

[ti_mouton]

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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...

[parmi]

La chaîne est trop longue pour VBA.

Regarde comment tu pourrais faire

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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)))"

[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

[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.

[ti_mouton]

La chaîne est trop longue pour VBA.

Regarde comment tu pourrais faire

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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