Autres pour toute version d excel eliminer les #NA du result d'une formule perso

patricktoulon

XLDnaute Barbatruc
Bonjour a tous
au jourd'hui je fait mumuse avec le return en matricielle d'une fonction perso mais j'ai un soucis pour éliminer le #N/A
pour vous donner un exemple
ma fonction
VB:
Function tbl(rng)
    tbl = rng.Value
End Function
Bien sur en vrai elle est beaucoup plus complexe que ça c'est juste pour l'exemple

en colonne A j'ai une liste et en c je met ma formule en matricielle sur toute la hauteur de la liste originale
la formule utilisée =tbl(A1:A7) autrement dit je ne prends pas toute la liste

j'ai bien essayé le si.non.disp ou meme le si(estna(.....
rien n'y fait
des idées ???

originalmatricielle
totototo
titititiformule validée en matricielle:=tbl(A1:A7)
riririri
fifififi
loulouloulou
tructruc
bidulebidule
machin
#N/A​
chouette
#N/A​
blablabla
#N/A​
toto
#N/A​
taratata
#N/A​
turlututu
#N/A​
chapeau
#N/A​
pointu
#N/A​
 

patricktoulon

XLDnaute Barbatruc
re
rebonjour à tous
voila un fichier qui montre comment je fait pour ne pas avoir les NA ou REF!
deux fonctions
TBL et TBLX

si je pouvais éviter l'erreur ca me plairait beaucoup
j'ai bien essayé de remplir les items en trop dans le code mais ça change rien
je vous laisse regarder
;)
 

Pièces jointes

  • exemple matriciel fonctions TBL et TBLX .xlsm
    16.8 KB · Affichages: 8

patricktoulon

XLDnaute Barbatruc
Bon ben voila si on veux pas gérer l'erreur il faut bien mettre les items restant a vide
VB:
Function TBLX(rng As Range)
    Dim x, y, T
    T = rng.Value
    On Error Resume Next
    If TypeName(Application.Caller) = "Range" Then
        If Err.Number = 0 Then
            x = Evaluate("ROW(1:" & Application.Caller.Rows.Count & ")")
            y = Evaluate("COLUMN(" & Cells(1).Resize(, Application.Caller.Column.Count).Address(0, 0) & ")")
            T = Application.Index(T, x, y)
        End If
    Else: Err.Clear: On Error GoTo 0
    End If
    For i = rng.Rows.Count + 1 To UBound(T): T(i, 1) = "": Next
    TBLX = T
End Function
plus besoins de la gestion "SIERREUR( ....) dans la formule
c'est quand même étonnant vu qu'ils sont vides
 

laurent950

XLDnaute Barbatruc
Re @patricktoulon

Ce n'est pas très concluant et assez complexe à écrire, ta formule si dessus en poste #20 est meilleur.

Comme j'avais effacé pensant que cela n'était pas correct je reposte :
=si(ESTERR(tbl(A1:A2));"";tbl(A1:A2))

Pour une formule personnalisée c'est compliqué, je préfère ta méthode en Poste #20

Merci Patrick
 

patricktoulon

XLDnaute Barbatruc
RE
regarde
1643918882047.png
 

Pièces jointes

  • fonction perso DicoAndCount.xlsm
    22.6 KB · Affichages: 6

patricktoulon

XLDnaute Barbatruc
pour avoir le formulaire de la fonction
a lancer a l'open
VB:
Sub descript()

    Application.MacroOptions _
            Macro:="DicoAndCountOrder", _
            Description:="Developed by Patricktoulon 03/01/2022" & vbCrLf & vbCrLf & _
                         "Argument 1 : plage de cellule à trier(doit etre une colonne)" & vbCrLf & vbCrLf & _
                         "Argument 2 : trier  1 pour trier les chaines  et 2 pour trier par les nombres d'occurences" & vbCrLf & vbCrLf & _
                         "Argument 3 : 1 pour croissant 2 pour décroissant", _
                         Category:=8, _
                         ArgumentDescriptions:=Array( _
                                               "Adresse de la colonne à trier", "2d argument :1 pour trier les chaines 2 pour les nombres d'occurences  ", _
                                               "bbbbbbbbbbbbe" & vbLf & "zzzzzzzzzzzzzzz")


End Sub
1643923372476.png
 

job75

XLDnaute Barbatruc
Bon avec UsedRange pas de problème de références circulaires, voyez cette fonction :
VB:
Function Matrice(R As Range)
'R doit être un vecteur colonne
Dim tablo, i&
tablo = R.Resize(R.Parent.UsedRange.Rows.Count + 1) 'matrice, plus rapide, au moins 2 éléments
For i = R.Count + 1 To UBound(tablo)
    tablo(i, 1) = ""
Next
Matrice = tablo 'vecteur colonne
End Function
Edit : on pourra vérifier que Matrice contient 21 éléments, 2 de plus que le nombre de cellules jaunes.

Bonne nuit.
 

Pièces jointes

  • Matrice(1).xlsm
    16.6 KB · Affichages: 4
Dernière édition:

patricktoulon

XLDnaute Barbatruc
re
@job75
oui exact dans le contexte de l'exemple que j'ai fourni
je pourrais tout aussi bien redimensionner mon tableau de sortie au usedrange
peu importe si il est plus grand que le range caller

mais si les formules devaient aller sur une autre feuille ben on est chocolat ;)je devrais utiliser le usedrange de la feuille destination en espérant qu'il y est assez de lignes
 

Bernard_XLD

XLDnaute Barbatruc
Membre du Staff
Bonjour patrick, dysorthographie, job75, laurent950, le forum

mais si les formules devaient aller sur une autre feuille ben on est chocolat ;)je devrais utiliser le usedrange de la feuille destination en espérant qu'il y est assez de lignes

@patrick , en utilisant ThisCell, pas ce problème.
tu peux retailler le tableau à la taille réelle de la matricielle

Bien cordialement, @+

VB:
Function TBL(rng)
Dim TBL_Temp, TBL_Temp2(), i&, x&, y&
TBL_Temp = rng.Value
With Application.ThisCell
    For i = 1 To .End(xlDown).Row - .Row + 1
        If Not .FormulaArray = .Offset(i, 0).FormulaArray Then Exit For
    Next i
End With
ReDim TBL_Temp2(1 To i, 0)
y = UBound(TBL_Temp, 1)
For x = 1 To i
    If x > y Then TBL_Temp2(x, 0) = "" Else TBL_Temp2(x, 0) = TBL_Temp(x, 1)
Next x
TBL = TBL_Temp2
End Function
Animation.gif
 
Dernière édition:

patricktoulon

XLDnaute Barbatruc
Bonjour @Yeahou ça fait longtemps 😁
pourquoi tout ce moulin ?
le application.caller donne la plage entière

VB:
Function TBLX(rng As Range)
    Dim X, X1, X2, T
    X1 = rng.Rows.Count
    X = Application.Caller.Rows.Count: X2 = Evaluate("ROW(1:" & X & ")")
   
 'on redimensionne le tableau simplement avec app.index 
     ' qui nous permet de redimentionner les deux dimension à l'inverse de redim preserve 
T = Application.Index(rng.Value, X2, Array(1))
    
 For a = X1 + 1 To UBound(T): T(a, 1) = "": Next:'obligatoire faut bien vider les items supplémentaires 
    TBLX = T
End Function
 

Statistiques des forums

Discussions
313 260
Messages
2 096 636
Membres
106 694
dernier inscrit
hussor.t