Parcourir un tableau et transcrire la fonction INDEX() en VBA

Version imprimable

Bonjour,
J'aimerai faire une fonction qui renvoie une valeur contenue dans un tableau en fonction de l'intitulé de sa ligne et sa colonne.

Premier problème je n'ai aucune idée de comment parcourir le tableau (j'ai regardé l'aide F1 à Range, mais je ne parviens pas à trouver, et je ne sais pas s'il y a vraiment une solution).

Si j'arrivais à définir "Premiere_ligne", "Derniere_ligne" et "Premiere_colonne" et "Derniere_Colonne" dans le code, idéalement le code serait:
Code:

1 2 3 4 5 6 7 8 9 10 11 12 Public Function Val_Index(tablo As Range, titre_ligne As String, titre_col As String) As Variant Dim ligne, num_ligne, Premiere_ligne, Derniere_ligne As Integer Dim colonne, num_col, Premiere_colonne, Derniere_colonne As Integer For ligne = Premiere_ligne To Derniere_ligne If Index(tablo, ligne, 1) = titre_ligne Then num_ligne = ligne For colonne = Premiere_colonne To Derniere_colonne If Index(tablo, 1, colonne) = titre_col Then num_col = colonne Next colonne Next ligne Val_Index = Index(tablo, num_ligne, num_col) End Function
Mais l'autre problème c'est que même si la fonction Index() s'écrit bien comme ça en VBA lorsqu'on la met dans une Range().FormulaR1C1= (je l'ai testé en enregistrant une macro), ça n'a pas l'air de marcher hors d'une cellule excel.

Voilà, je ne suis pas très avancée, même pas du tout, pour cette fonction, mais je ne sais vraiment pas comment m'y prendre.
Si quelqu'un accepte de donner un coup de main, je l'en remercie d'avance.

14/01/2011, 17h20
Jean-Philippe André

salut,

qu'entends tu par "l'intitulé de sa ligne et sa colonne" stp ?

Avant de passer a la question 2, j'aimerais au moins comprendre la question 1 :aie:
14/01/2011, 17h20
pgz

Bonjour.

Si tablo représente une zone de la feuille, en VBA tu trouves la cellule de la ième ligne et jème colonne avec

Code:

Tablo.cells(i,j)

En espérant que cela t'aide,

PGZ

Salut
Ça ressemble fortement a une fonction de base de donnée, je suis étonné qu'elle n'existe pas déjà.
Tu veux dans une sélection contenant une colonne et une ligne d’entêté, obtenir l'intersection d'une ligne et d'une colonne en fournissant le texte contenu en entête de colonne et celui contenu en entête de ligne.
Je vais regarder

[Edit]
Humm pas trouvé ca m'etonne...
Essai avec ca
Code:

1 2 3 4 5 6 7 8 Function test(Tablo As Range, TexteRow As String, TexteCol As String) As String Dim nRow As Long, nCol As Integer nCol = WorksheetFunction.Match(TexteRow, Tablo.Rows(1), 0) nRow = WorksheetFunction.Match(TexteCol, Tablo.Columns(1), 0) test = Tablo.Cells(nRow, nCol) End Function
Le code peut etre amélioré, il faudrait vérifier si plusieurs lignes ou colonnes n'ont pas les valeurs de textecol et texterow en entête.

14/01/2011, 21h50
Didier Gonard

Bonjour,

serais-ce ça que tu cherches ?

Citation:

Référence du développeur Excel
WorksheetFunction.Index, méthode
Afficher tout
Masquer tout
Renvoie une valeur ou la référence à une valeur à partir d'une table ou d'une plage de valeurs. Il existe deux formes de fonction INDEX : la forme tableau (matrice : permet de créer des formules uniques permettant d’obtenir plusieurs résultats et qui agissent sur un groupe d’arguments répartis dans des lignes et des colonnes. Une plage matricielle partage une même formule tandis qu’une constante matricielle est un groupe de constantes qui sert d’argument.) et la forme référence.
Syntaxe

expression.Index(Arg1, Arg2, Arg3, Arg4)

expression Variable représentant un objet WorksheetFunction.

Paramètres

Nom Obligatoire/Facultatif Type de données Description
Arg1 Obligatoire Variante Array ou Reference - plage de cellules d'une constante de tableau. Pour référence, il s'agit de la référence à une ou plusieurs plages de cellules.
Arg2 Obligatoire Double Row_num - sélectionne la ligne du tableau à partir de laquelle la valeur doit être renvoyée. Si row_num est omis, column_num doit être indiqué. Pour référence, le nombre de la ligne en référence à partir de laquelle une référence doit être renvoyée
Arg3 Facultatif Variante Column_num - sélectionne la colonne du tableau à partir de laquelle la valeur doit être renvoyée. Si column_num est omis, row_num doit être indiqué. Pour référence, le nombre de la colonne en référence à partir de laquelle une référence doit être renvoyée
Arg4 Facultatif Variante Area_num - utilisé uniquement pour renvoyer des références. Sélectionne une plage de valeurs en référence à partir de laquelle renvoyer l'intersection de row_num et de column_num. La première zone sélectionnée ou saisie est numérotée 1, la deuxième 2, etc. Si area_num est omis, la méthode INDEX utilise la zone 1.

Valeur renvoyée
Variante
etc... cf aide VBA -

ajout : Sinon sous 2010, la notion de tableau a fortement évoluée / 2003, avec un onglet spécifique Outils de tableau et des correspondances VBA donc savoir de quoi il s'agit..

cordialement,

Didier
17/01/2011, 10h50
statista

Bonjour,
Merci pour vos réponses

@pgz, oui merci ça résoud entièrement le deuxième problème

@jpcheck: pour être plus limpide, Qwazerty a reformulé exactement ce que je cherchais à faire, donc je le cite:

Citation:

dans une sélection contenant une colonne et une ligne d’entêté, obtenir l'intersection d'une ligne et d'une colonne en fournissant le texte contenu en entête de colonne et celui contenu en entête de ligne

(les entêtes de colonne étant dans la première ligne, et les entêtes de ligne étant dans la première colonne du tableau)

@Qwazerty: merci beaucoup pour le code, il semble marcher (pas de bug) par contre j'obtiens "#VALEUR!" en réponse (même en redéfinissant le résultat "as variant"), mais je vais chercher à quoi c'est dû.

@Ormonth, merci, en fait j'essaye de faire la même chose que cette fonction mais avec en argument les titres de lignes et de colonnes et non leurs numéros (car les données que j'utilise ne sont pas toujours dans le même ordre ni les mêmes champs)

Je pense qu'avec toutes ces pistes je vais m'en sortir merci beaucoup.

En fait j'ai une erreur d'execution pour le code Qwazerty: "Impossible de lire la prorpiété "Match" de la classe WorksheetFunction"

Par contre j'ai pu faire un code qui marchait (sans doute pas le plus simple mais bon):

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
Public Function Val_Index(Tablo As Range, titre_ligne As String, titre_col As String) As Variant
Dim ligne, num_ligne, Premiere_ligne, Derniere_ligne As Integer
Dim colonne, num_col, Premiere_colonne, Derniere_colonne As Integer
Dim test1, test2 As Integer
Derniere_ligne = Tablo.Rows.Count
Derniere_colonne = Tablo.Columns.Count
    For ligne = 2 To Derniere_ligne
        If Tablo.Cells(ligne, 1) = titre_ligne Then num_ligne = ligne
        For colonne = 2 To Derniere_colonne
            If Tablo.Cells(1, colonne) = titre_col Then num_col = colonne
        Next colonne
    Next ligne
    Val_Index = Tablo.Cells(num_ligne, num_col)
End Function

Merci à tous

Salut

J'ai eu un peu le même soucis sur un autre poste, j'utilisais Match sur des cellules contenant des données numériques et Match ne semble pas faire la conversion de lui même si tu lui demandes de chercher un texte, ainsi
Match(2,....) et Match("2",....) ne retourneront pas le même résultat. Pire que ça, la fonction semble planter et ne même pas retourner comme prévu #N/A si la valeur n'est pas trouvé...

[Edit]
Attention a tes déclarationsde variables en VB

Code:

Dim Variable1, Variable2, Variable3 as integer

est équivalent à

Code:

Dim Variable1 as variant, Variable2 as variant, variable3 as integer

Autre petit truc, il est pratique de mettre de majuscule dans tes noms de variables, comme ça quand tu tapes tes lignes de code, si au moment de retour a la ligne le nom de ta variable n'a pas de majuscule, c'est que tu as fais une erreur en tapant.

Sinon essai ce code
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 Public Function Val_Index(Tablo As Range, Titre_Ligne As String, Titre_Col As String) As Variant Dim Num_Ligne As Long, Num_Col As Long Dim TheCell As Range 'On recherche dans toutes les cellules de la ligne For Each TheCell In Tablo.Rows(1).Cells If TheCell.Value = Titre_Ligne Then Num_Col = TheCell.Column Exit For End If Next 'On recherche dans toutes les cellules de la colonne For Each TheCell In Tablo.Columns(1).Cells If TheCell = Titre_Col Then Num_Ligne = TheCell.Row Exit For End If Next 'Si on n'a pas trouvé de correspondance pour la colonne ou la ligne If (Num_Ligne = 0) Or (Num_Col = 0) Then 'On retourne une erreur Val_Index = CVErr(xlErrNA) Exit Function End If 'sinon on retourne la valeur a l'intersection Val_Index = Tablo.Cells(Num_Ligne, Num_Col) End Function
[/Edit]
++
Qwaz

18/01/2011, 16h44
statista

Merci pour ces conseils et précisions, en effet certains de mes titres sont numériques (comme l'année) ce qui expliquerait l'erreur.
Ce nouveau code marche bien, merci.