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

[patricktoulon]

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

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

original		matricielle
toto		toto
titi		titi	formule validée en matricielle:=tbl(A1:A7)
riri		riri
fifi		fifi
loulou		loulou
truc		truc
bidule		bidule
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]

 

[dysorthographie]

bonjour Patrick,
désolé j'essais de reproduire ton problème mais je n'y arrive pas! je suis Excel 2007!

 

[job75]

Bonjour à tous,

Si l'on veut tenter d'éliminer les #N/A il faut forcément utiliser Application.Caller.

Et cela crée des références circulaires, donc je pense que ce problème n'a pas de solution.

A+

 

[patricktoulon]

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

 

[patricktoulon]

Bon ben voila si on veux pas gérer l'erreur il faut bien mettre les items restant a vide

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

 

[patricktoulon]

re
oui laurent
faut voir la conso après selon les formules

 

[laurent950]

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]

RE
regarde

 

[laurent950]

Re
Je viens de regarder, ton code est excellent Patrick, Bravo c'est pointu.
Laurent

 

[patricktoulon]

pour avoir le formulaire de la fonction
a lancer a l'open

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

 

[job75]

Bon avec UsedRange pas de problème de références circulaires, voyez cette fonction :

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.

 

[patricktoulon]

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]

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, @+

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

 

[patricktoulon]

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

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

 

[Bernard_XLD]

pourquoi tout ce moulin ?
le application.caller donne la plage entière

je n'y ai pas pensé tout simplement, mais le principe est le mème