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]

je pensais aussi me simplifier la vie comme ça , mais visiblement ça ne fonctionne pas
et je ne sais pas pourquoi

Function TBLX2(rng As Range)
    Dim t1, t2, L&, C&: t1 = rng.Value: t2 = Application.Caller.Value
    'MsgBox UBound(t2)'c'est bon le t2 est bien un tableau
    For L = 1 To UBound(t2)
        For C = 1 To UBound(t2, 2)
           't2(L, C) = ""'on le vide pour éviter le 0 ou  NA ou REF!
            If L <= UBound(t1) And C <= UBound(t1, 2) Then t2(L, C) = t1(L, C)
        Next
    Next
    TBLX2 = t2
End Function

 

[Bernard_XLD]

Re,
je viens de tester, le application.caller implique une référence circulaire que l'on a pas avec ThisCell.
Ma fonction du post 28 fonctionne mais plante dès que j'essaye de remplacer la boucle ThisCell par application.caller.rows, je creuse pour voir si je peux contourner

 

[job75]

Bonjour patricktoulon, le forum,

@job75
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

Chocolat ? Quand tu affirmes une chose teste avant, ce fichier (2) ne pose pas de problème.

A+

 

[patricktoulon]

@Yeahou visiblement quand on utilise un tableau secondaire ca ne fonctionne pas
c'est pour cela que je resize le tableau de (l'argument) puisqu'il est deja rempli
reste plus qu'a mettre bien a vide les items ajouté ligne et colonnes

 

[job75]

La plage R et la matrice peuvent avoir des dimensions quelconques.

Voyez le fichier (3) joint et cette fonction :

Function Matrice(R As Range)
'R est une plage rectangulaire quelconque
Dim nlig&, ncol%, tablo, i&, j%
nlig = R.Rows.Count: ncol = R.Columns.Count
tablo = R.Resize(R.Parent.UsedRange.Rows.Count + 1, ncol) 'matrice, plus rapide, au moins 2 éléments
For i = 1 To UBound(tablo)
    For j = 1 To ncol
        If i > nlig Or j > ncol Then tablo(i, j) = ""
Next j, i
Matrice = tablo
End Function

 

[mapomme]

Bonjour à tous

Bizarre @patricktoulon
Ta fonction TBLX2 ne me retourne que des zéros ou bien une référence circulaire

Mais La fonction tbl ci-dessous me renvoie le résultat attendu.

Function tbl(rng As Range)
    Dim t, i&, j&
    ReDim r(1 To Application.Caller.Rows.Count, 1 To Application.Caller.Columns.Count)
 
    t = rng.Value
    For i = 1 To UBound(r): For j = 1 To UBound(r, 2): r(i, j) = "": Next j, i
    For i = 1 To UBound(t): For j = 1 To UBound(t, 2): r(i, j) = t(i, j): Next j, i
    tbl = r
End Function

 

[patricktoulon]

visiblement la plus simple est bien ma version de base
a savoir redimensionner le tableau (argument)
la plage caller peut faire plus de lignes ou plus de colonnes que l'argument c'est nikel!

la fonction commentées
c'est assez simple et je peux utiliser le application.caller

Function TBLX(RnG As Range)
    Dim  X1&, X2, y1&, Y2, X&,C&,T
    
    X1 = RnG.Rows.Count 'nombre de lignes du tableau  by RnG
    
    y1 = RnG.Columns.Count 'nombre de colonnes du tableau  by RnG
    
    X2 = Evaluate("ROW(1:" & Application.Caller.Rows.Count & ")")    'création array 2 dimensions(x lignes = nombre de colonne du caller)
    
    y2 = Application.Transpose(Evaluate("ROW(1:" & Application.Caller.Columns.Count & ")"))    'création array 1 dimension(x items = nombre de colonne du caller)

    'redimensionnement du tableau rng
    'shemas:=Application.Index(  [tableau original]  , [array 2 dim pour les lignes]  ,   [ array 1 dim pour les colonnes]   )
    T = Application.Index(RnG.Value, X2, y2)

    For a = 1 To UBound(T) 'boucle sur les lignes du tableau
        For C = 1 To UBound(T, 2) 'boucles sur les colonne  du tableau
            If a > X1 Or C > y1 Then T(a, C) = "" ' si on depasse les dimensions originales (lignes ou colonnes) on clear l'items du tableau
        Next C
    Next a
    TBLX = T
End Function

démonstration :je prends beaucoup plus en ligne et colonne que l'argument

bien entendu la vrai fonction est plus complexe c'est juste une demo pour montrer que l'on a plus les N/A ou REF! ou 0 selon la fonction

il me parait de plus en plus evident (sauf erreur de ma part
que l'utilisation d'un 2d tableau pour transférer n'est pas la solution la plus simple vu que que par la force des choses une formule matricielle implique une répétition et donc les indexs sont faussés
je dis bien (sauf erreur de ma part ou mauvaise interprétation du comportement

conclusion ma solution semble etre la bonne
a savoir redimer lignes et colonnes le tableau de l'argument et vider tout les items ligne et colonnes) qui n'y etaient pas au depart

 

[mapomme]

Re,

Une petite modif de la fonction tbl() pour afficher "" pour les cellules de la source qui sont vides.

Function tbl(rng As Range)
    Dim t, i&, j&
    ReDim r(1 To Application.Caller.Rows.Count, 1 To Application.Caller.Columns.Count)
   
    t = rng.Value
    For i = 1 To UBound(r): For j = 1 To UBound(r, 2): r(i, j) = "": Next j, i
    For i = 1 To UBound(t): For j = 1 To UBound(t, 2): r(i, j) = IIf(t(i, j) = "", "", t(i, j)): Next j, i
    tbl = r
End Function

 

[Bernard_XLD]

visiblement la plus simple est bien ma version de base

Re,

elle ne fonctionne qu'à partir d'une entrée range tout comme celle de job75, ce qui n'est pas le cas de la mienne dont le principe fonctionnera avec un array ou une matrice calculée dans la fonction, et qui pourra être rendue compatible matricielles dynamiques ou le surdimensionnement du tableau de sortie pose problème.

Bien cordialement, @+

 

[job75]

visiblement la plus simple est bien ma version de base
a savoir redimensionner le tableau (argument)

Ta version de base n'est pas la plus simple.

Et bien sûr redimensionner le tableau (argument) est indispensable, c'est la seule solution.

 

[patricktoulon]

re
mapomme ca marche aussi
va savoir toi

@job75

Et bien sûr redimensionner le tableau (argument) est indispensable, c'est la seule solution.

non pas forcement @mapomme démontre que l'utilisation d'un autre tableau fonctionne aussi

Ta version de base n'est pas la plus simple.

c'est la seule avec la quelle je n'ai pas de soucis sur 2007 2013 2016 (les 3 en 32 bits)

@Yeahou
argument range ou variable() variant
oui bien évidemment ce switch là je le met après , le code est déjà prêt

 

[mapomme]

Re,
Une v3 qui tient compte que la source puisse être une cellule unique.

Function tbl(rng As Range)
Dim t, x, i&, j&
ReDim r(1 To Application.Caller.Rows.Count, 1 To Application.Caller.Columns.Count)
    t = rng.Value
    If Not IsArray(t) Then x = t: ReDim t(1 To 1, 1 To 1): t(1, 1) = x
    For i = 1 To UBound(r): For j = 1 To UBound(r, 2): r(i, j) = "": Next j, i
    For i = 1 To UBound(t): For j = 1 To UBound(t, 2): r(i, j) = IIf(t(i, j) = "", "", t(i, j)): Next j, i
    tbl = r
End Function

 

[mapomme]

re
mapomme ca marche aussi
va savoir toi

Et pourtant c'est ce qu'on aimerait bien comprendre

 

[patricktoulon]

@mapomme
exemple avec un dico chaine /nbres d'occurences (return x lignes sur 2 colonnes )
le range caller peut être une ou 2 colonnes
basé sur ton exemple 1

Function tblmapomme(rng As Range)
    'base @mapomme + dico patricktoulon
    Dim t, i&, j&, dico As Object, r
    ReDim r(1 To Application.Caller.Rows.Count, 1 To 2)
    Set dico = CreateObject("Scripting.Dictionary")
    t = rng.Value
    For i = 1 To UBound(t): dico(t(i, 1)) = Val(dico(t(i, 1))) + 1: Next    'enregistrement dans dico  key chaine;items nbres d'occurences
    k = dico.keys: it = dico.items    'dico to array
    For i = 1 To UBound(r) 'on clear les items du tableau
        For j = 1 To UBound(r, 2): r(i, j) = ""
        Next j
    Next i
   
    For i = 0 To UBound(k): r(i + 1, 1) = k(i): r(i + 1, 2) = it(i): Next
    tblmapomme = r
End Function

Et pourtant c'est ce qu'on aimerait bien comprendre

oui selon comment tu aborde la chose en terme d'organigramme dans le code ben tu es chocolat
j'ai fourni tout au long de cette discussion des exemples qui ne fonctionnaient plus sans raison logique
mais bon

je pense avoir 2/3 solutions qui sont fiables
la mienne parce que c'est moi qui est trouvé cette méthode
j'en ai d'ailleurs fait une fonction perso redimpreserveXY qui me permet de redimensionner ligne ou/et colonne

la tienne semble simple peut être un poil plus rapide (faut voir)

 

[patricktoulon]

pour en revenir a ce que disait @Yeahou tout a l'heure
a savoir un switch sur l'argument (range ou variant() ) et (caller ou sub )
la tblmapomme fonctionnant en sub et en formule matricielle

Sub test()
    X = tblmapomme([A1:A15].Value)
[F1].Resize(UBound(X), 2) = X
End Sub

Function tblmapomme(rng As Variant)
'base @mapomme + dico patricktoulon
    Dim t, i&, j&, dico As Object, r
    If TypeName(rng) = "Range" Then t = rng.Value Else t = rng
    On Error Resume Next
    ReDim r(1 To Application.Caller.Rows.Count, 1 To 2)
    If Err.Number > 0 Then ReDim r(1 To UBound(rng), 1 To 2)
    On Error GoTo 0
    Set dico = CreateObject("Scripting.Dictionary")
    For i = 1 To UBound(t): dico(t(i, 1)) = Val(dico(t(i, 1))) + 1: Next    'enregistrement dans dico  key chaine;items nbres d'occurences
    k = dico.keys: it = dico.items    'dico to array
    For i = 1 To UBound(r)'on clear les items du tableau
        For j = 1 To UBound(r, 2): r(i, j) = ""
        Next j
    Next i
    For i = 0 To UBound(k): r(i + 1, 1) = k(i): r(i + 1, 2) = it(i): Next
    tblmapomme = r
End Function