Stockage du résultat d'une formule matricielle en VBA
Bonjour,
voilà plusieurs heures que je suis coincé sur un problème de stockage de donnée en VBA en provenance d'une formule matricielle, j'ai donc besoin de votre aide.
J'ai une feuille de calcul qui contient une liste de personne avec des adresses. Chaque personne est identifié via un numéro et peux avoir plusieurs adresses (et donc plusieurs lignes).
J'ai besoin de récupérer l'ensemble des numéros de lignes dans lequels ce trouve chaque occurence d'une personne.
Pour cela j'utilise la formule matricielle suivante en ayant déclaré ma variable tabl:
Dim tabl(5) as Variant
tabl = Evaluate("IFERROR(SMALL(IF(C[-5]=""00606051"",ROW(C[-5]),""""),{1;2;3;4;5;6}),"""")")
"00606051" est l'un des numéros authentifiant une personne
Bien entendu cela ne fonctionne pas car cela me dis impossible d'affecter à un tableau.
De plus j'aimerais remplacer C[-5] par une référence plus direct à la colonne concerné.
Merci de votre aide.
Bonjour,
Difficile de voir ce que tu veux faire sans classeur !
Ce que je peux dire, c'est que j'utiliserais certainement une autre méthode.
Evidemment la syntaxe d'affectation à un tableau pré-dimensionné ne fonctionnera pas. Tu peux affecter une valeur (ou même un tableau) à un élément de tableau de Variant...
Cordialement
En effet avec un exemple cela devrait être plus clair. J'ai donc extrait de mon projet ce qui me pose problème.
Tu aurais peut-être dû tester la formule en manuel...
Comme on ne sait où tu veux en venir ensuite, dans le cadre strict de ta méthode :
Sub Macro()
'
' Macro
'
Dim test(5), i%
With Range("F2:F7")
.FormulaArray = _
"=IFERROR(SMALL(IF(RC[-5]:R[4]C[-5]=""00606051"",ROW(RC[-5]:R[4]C[-5]),""""),{1;2;3;4;5;6}),"""")"
For i = 0 To 5
test(i) = .Cells(i + 1, 1).Value
MsgBox test(i)
Next i
End With
End SubCordialement
Bonsoir à tous,
Pas tester la solution de MFerrand, je suis sous Excel 2003
Sinon :
Sub test()
Dim x
With Sheets("Feuil1")
With .Range("a1", .Range("a" & Rows.Count).End(xlUp))
x = Filter(Evaluate("transpose(if(" & .Address & "=""00606051"",row(1:" & _
.Rows.Count & "),char(2)))"), Chr(2), 0)
If UBound(x) > -1 Then
.Offset(1, .Columns.Count + 5).CurrentRegion.ClearContents
.Offset(1, .Columns.Count + 5).Resize(UBound(x) + 1, 5).Value = _
Application.Index(.Resize(, 5).Value, Application.Transpose(x), [{1,2,3,4,5}])
Else
MsgBox "Pas de données"
End If
End With
End With
End SubOu ceci :
Sub test1()
Dim x
With Sheets("Feuil1")
With .Range("a1", .Range("a" & Rows.Count).End(xlUp))
x = Filter(Evaluate("transpose(if(" & .Address & "=""00606051"",row(1:" & _
.Rows.Count & "),char(2)))"), Chr(2), 0)
If UBound(x) > -1 Then
.Offset(1, .Columns.Count + 5).CurrentRegion.ClearContents
.Offset(1, .Columns.Count + 5).Resize(UBound(x) + 1).Value = Application.Transpose(x)
Else
MsgBox "Pas de données"
End If
End With
End With
End Subklin89
Merci cela répond a une partie du problème.
Par contre je ne veux pas inscrire la formule dans des cellules mais directement dans une variable pour ensuite l'utiliser pour aller chercher des infos dans les autres colonnes des lignes ainsi remontés.
Comment je peux faire ?
C'est pourtant ce que tu faisais dans ta macro... Comme je disais, si tu commençais par détailler là où tu veux en venir...
Parce que récolter des numéros de ligne, et pour un seul élément de ta base, ne peut être qu'une étape, et le numéro de ligne n'est qu'un repère pour autre chose...
J'ai tendance à considérer que, sauf exception, on gagne à éviter les interférences entre commandes du tableur et commandes proprement VBA. Extraire par des moyens simples, voire simplistes (le plus souvent au moyen de boucles), les éléments qu'on veut traiter, opérer le travail sur ces éléments en VBA sans interaction avec le tableur, et réaffecter le résultat final, constitue souvent la solution la plus rapide, ou au moins la plus simple.
Cordialement
Oui je me rends bien compte que je suis pas très clair donc désolé.
En gros je cherche à analyser une table de donnée pour trouver à l'intérieur chaque ligne qui contient une personne connue à l'avance (via un autre bout de code).
Une fois ces lignes identifiés j'ai donc besoin de les stockés dans une variable pour savoir dans quelles lignes je dois agir.
Or mon problème est que je n'arrive pas à exécuter la formule matricielle ailleurs que dans des cellules d'une feuille excel.
J'ai donc besoin d'éxécuter cette formule matricielle uniquement dans le code VBA et de récupérer ce qu'elle retourne dans une variable mais j'ai de plus en plus l'impression que cela n'est pas possible.
Dites moi que je me trompe et que c'est faisable (si en plus vous me dites comment ce serait le top du top)
En tout cas merci pour votre patience et votre attention.
Une formule est principalement utile pour afficher et mettre à jour des valeurs selon les variations de cellules dont elle dépend. Son utilisation en VBA n'est en fait qu'un détournement, pas toujours adapté.
Pour récupérer des éléments dans une base, à partir d'une caractéristique commune, il y a divers moyens ; l'un des plus rapides consiste à : trier la base sur cette caractéristique, ce qui regroupera les éléments qui la possèdent, en rechercher le premier [jusque là, on utilise toujours en fait des fonctionnalités du tableur, mais c'est inévitable pour pouvoir en extraire des données], ensuite une boucle simple (jusqu'à épuiser les éléments disposant de la caractéristique recherchée) permet de récupérer, soit dans un tableau, soit en les concaténant dans une variable de type variant avec un séparateur qui permettra ensuite d'en faire un tableau en "splittant", les données de chacun des éléments sur lesquelles on veut agir (car le numéro de ligne n'a pas d'intérêt autre que d'être un repère pour trouver autre chose, donc autant prélever cette "autre chose" directement).
Cordialement