Discussion :

[chipster62]

Bonjour à tous,

J'ai besoin d'utiliser la fonction index et equiv pour recherche la valeur d'une cellule par rapport à 2 critères de recherche.

Si je me saisie "manuellement" les formules cela fonctionne :
En F2 : {=INDEX(Durées!H:H;EQUIV(A2&B2;Durées!A:A&Durées!E:E;0))}
En F3 : {=INDEX(Durées!H:H;EQUIV(A3&B3;Durées!A:A&Durées!E:E;0))}
etc...

Je n'arrive pas à faire le calcul en VBA automatiquement dans une boucle.

Pour me permettre de "boucler le résultat" sur des milliers de lignes et aussi lancer automatique le calcul en formule matricielle.
J'aimerais si possible avoir le résultat immédiat et non pas la saisie de la formule dans les cellules F.

Pouvez vous m'aider à résoudre ce soucis, si cela est possible bien sur.

[antonysansh]

• Tu te mets sur F2
• lance l'enregistreur de macro
• F2
• CTRL+MAJ+Enter (car matricielle)

Et tu as un code de ce genre :

Code :

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

1
2
3
4
5
6
7
8
9
Sub Macro1()
'
' Macro1 Macro
'
 
'
    Selection.FormulaArray = _
        "=INDEX(Durées!C[6],MATCH(RC[-1]&RC,Durées!C[-1]&Durées!C[3],0))"
End Sub

C'est une bonne piste à mon sens. Mais si vraiment beaucoup de ligne, à voir si tu ne peux pas faire directement avec des fonctions en VB.

A lire si pas déjà fait, ce tuto de Pierre Fauconnier est géniale.

[chipster62]

Oui mais le problème c'est le temps d'attente entre chaque cellule (de plus c'est la formule qui s'inscrit)

Le temps est trop long à mon gout : pratiquement 1 seconde pour 3 ou 4 lignes : j'ai actuellement plus de 12000 lignes dans mon tableau.

Donc je cherche aussi à gagner du temps en vba, ce n'est pas pour en perdre lors de cette étape.
Donc je cherche une solution plus rapide que ce code :

Code :

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

1
2
3
4
5
ligne = Cells.End(xlDown).Row
For i = 2 To ligne
Cells(i, 6).Select
Selection.FormulaArray = "=INDEX(Durées!C[2],MATCH(RC[-5]&RC[-4],Durées!C[-5]&Durées!C[-1],0))"
Next i

[clementmarcotte]

Bonjour,

Oui mais le problème c'est le temps d'attente entre chaque cellule (de plus c'est la formule qui s'inscrit)

Le temps est trop long à mon gout : pratiquement 1 seconde pour 3 ou 4 lignes : j'ai actuellement plus de 12000 lignes dans mon tableau.

Donc, la question fondamentale à se poser c'est de savoir si Excel est vraiment l'outil le plus approprié pour la tâche à effectuer.

Donc je cherche aussi à gagner du temps en vba, ce n'est pas pour en perdre lors de cette étape.

À moins d'utiliser une combinaison extrême de formules, ou une formule vraiment mal construite;, les fonctions intrinsèques d'Excel sont plus performantes que VBA. Juste parce que les fonctions d'Excel sont compilées et que VBA est interprété.

Et puis, la grande "faiblesse" d'Excel avec les gros fichiers, c'est qu'Excel doit avoir tout le fichier en mémoire vive pour procéder. Alors qu'Access est beaucoup plus tolérant qu'Excel à cet égard. Et puis, je suis très porté à penser qu'une banale requête SQL (dans Access, ou bien à partir d'un autre classeur Excel avec ton classeur actuel comme source de données, ou bien dans VB.net, toujours avec ton classeur Excel comme source de données) serait bien plus efficace.

D'autant plus que Visual Studio Community est gratuit.

[chipster62]

Access n'est pas une corde à mon arc.
mes connaissances à Access remonte à mes années lycées... (plus de 20 ans )

Sur Excel, j'ai pratiquement arrivé (en je pense)

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
ligne = Cells.End(xlDown).Row
For i = 2 To ligne
'Next i
'Formule excel origine à trouver en vba =INDEX(Durées!H1:H164850;EQUIV(A2&B2;Durées!A1:A164850&Durées!E1:E164850;0))
a = Cells(i, 1)
b = Cells(i, 2)
With Application.WorksheetFunction
var1 = .Match(a, Worksheets("Durées").Range("A1:A164850"), 0)
var2 = .Match(b, Worksheets("Durées").Range("E1:E164850"), 0)
Cells(i, 6).Value = .Index(Sheet4.Range("H1:H164850"), var1, var2)
End With
Next i

En testant pas à pas mon code, il me trouve bien les variables a, b et var1
mais c'est var2 qui coince.

Pour var1, il me donne bien le numéro de la 1ère ligne correspondant à ma cellule de la variable a dans ma table de la feuille durées.
Malgré le peu de différence avec var1, var2 me rejette en erreur.

[Marc-L]

Bonjour !

WorksheetFunction est inutile car source d'erreur en cas de non correspondance !

Mieux vaut affecter une variable Variant par une fonction d'Excel directement via Application puis

la tester via les fonctions VBA IsError ou IsNumeric … Exemple dans les lignes n°4 & 5 de ce code.

_____________________________________________________________________________________________________
Je suis Charlie, Bardo, Sousse