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​

 

[laurent950]

Bonjour @patricktoulon

As tu essayé cette formule : A1 = 0 et B1 = 10 soit 0/10 = #DIV/0!
=SI(ESTERREUR(A1/B1);0;A1/B1)
tu peux remplacer le 0 par "" | =SI(ESTERREUR(A1/B1);"";A1/B1)

Cela fonctionne aussi pour #NA!

en VBA : IF(ISERROR(B1/A1 ),0,B1/A1 )

 

[patricktoulon]

bonjour Laurent
??????????

 

[TooFatBoy]

Bonjour,

As tu essayé cette formule : A1 = 0 et B1 = 10 soit 0/10 = #DIV/0!

Euh... Are you sure ???

Je crois que ce qu'il veut, même si ne vois pas bien l'intérêt, c'est que sa fonction ne renvoie justement pas #N/A.


[edit]
Disons que ce message #4 n'existe pas... que je ne l'ai jamais posté et même jamais écrit...
[/edit]

 

[laurent950]

Re @patricktoulon

il ne faut pas noté sierreur, la fonction c'est si esterreur c'est différent

Fonctions SI/ESTERREUR dans Excel

Fonctions SI/ESTERREUR dans Excel

affordsol.be

La fonction : ESTERREUR
Renvoie VRAI si l'exécution de la formule résulterait en une erreur #DIV/0, #N/A, etc.
{=SI(ESTERREUR(tbl(A2:A7));"";tbl(A2:A7))}
comme dans l'exemple

 

[Dranreb]

Bonjour.
En convertissant par CInt ou CLng un Variant valant #N/A on tombe sur la constante xlErrNA.
Bien que ce soit l'inverse de l'objectif, CVerr(xlErrNA) fabrique cette valeur.

Sub test()
   MsgBox CInt(CVErr(xlErrNA)) & " = " & xlErrNA
   End Sub

Function TblNonNA(ByVal Rng As Range) As Variant()
   RngValNonNA(TblNonNA) = Rng
   End Function
Private Property Let RngValNonNA(T(), ByVal RHS As Range)
   Dim L As Long, C As Integer
   T = RHS.Value
   For L = 1 To UBound(T, 1): For C = 1 To UBound(T, 2)
      If IsError(T(L, C)) Then If CInt(T(L, C)) = xlErrNA Then T(L, C) = Empty
      Next C, L
   End Property

 

[patricktoulon]

tu peux etre plus précis laurent
la formule de base c'est =tbl(A2:A7) etendue sur plus de lignes que de résultats et en matricielle

cela dit j'ai appliqué l'astuce que nous utilisons @Yeahou et moi a savoir redimensionner le tableau plus grand

avec mon astuce evaluate je crée un tableau de nombres de 1 à 65535 de 2 dimensions(65535 lignes,1 colonne)
avec mon astuce application.index ou l'on peut redimensionner n’importe quelle dimension je redimensionne le tableau
résultat
j'obtient des #REF! apres les valeurs et plus des #N/A

que je peux gérer avec SIERREUR

DONC LA FONCTION

Function tbl(rng)
    Dim tbl2, T
    tbl = rng.Value
   'astuce 1 on cré un array (2 dimensions (MAX DE LIGNES POUR UN XLS,1 colonne)) de nombres de 1  à 65535
    x = Evaluate("row(1:65535)")
    ' astuce 2 je redimensionne MAINTENANT le tableau avec (l'astuce 1) pour les lignes et (array(1)) pour  1 colonne
    tbl2 = Application.Index(tbl, x, Array(1))
    tbl = tbl2
End Function

bon bien sur on est limité a 65535 lignes mais bon
démonstration

 

[Dranreb]

J'utilise beaucoup ColUti et PlgUti ne tenant compte que de la 1ère ligne ou cellule de la plage spécifiée :

Private Function PlgUti(ByVal PlageDép As Range, Optional ByVal PlagExam As Range = Nothing, _
   Optional ByVal LMin As Long, Optional ByVal CMin As Long) As Range
   Dim LMax As Long, CMax As Long, NbL As Long, NbC As Long
   On Error GoTo RienTrouvé
   If PlagExam Is Nothing Then Set PlagExam = PlageDép.Worksheet.UsedRange
   LMax = PlagExam.Find("*", PlagExam.Cells(1, 1), xlValues, xlWhole, xlByRows, xlPrevious).Row
   CMax = PlagExam.Find("*", PlagExam.Cells(1, 1), xlValues, xlWhole, xlByColumns, xlPrevious).Column
   On Error GoTo 0
   NbL = LMax - PlageDép.Row + 1: If NbL < LMin Then NbL = LMin
   NbC = CMax - PlageDép.Column + 1: If NbC < CMin Then NbC = CMin
   If NbL < 1 Or NbC < 1 Then GoTo CEstToutVide
   Set PlgUti = PlageDép.Resize(NbL, NbC)
   Exit Function
RienTrouvé: Resume CEstToutVide
CEstToutVide: Set PlgUti = Nothing
   End Function
Private Function ColUti(ByVal PlageDép As Range, Optional ByVal LMin As Long, Optional ByVal CMin As Long) As Range
   Set ColUti = PlgUti(PlageDép, Intersect(PlageDép.Worksheet.UsedRange, PlageDép.EntireColumn), LMin, CMin)
   End Function

Pour un usage en fonction personnalisée il convient de spécifier toute la plage dont une cellule est susceptible d'avoir été modifiée pour qu'Excel détecte la nécessité de la réévaluer.

 

[Dranreb]

Mais je ne comprends pas trop le problème. Si on récupère la valeur d'une plage trop grande ça ne génère pas de #N/A comme si on affecte un tableau à une plage trop grande. Et si on la retourne dans une plage matricielle trop petite il est simplement tronqué.

Essayer ça :

Function Tbl(ByVal Rng As Range) As Variant()
   Tbl = Rng.Resize(Application.Caller.Rows.Count).Value
   End Function[Code]

Si ça ve convient pas car ça risque de ramener des valeur non désirées, il ne reste plus qu'à verser les valeurs élément par élément.

 

[patricktoulon]

re
tiens pour l'astuce de redim ligne ou colonne

Sub testx()
    Dim tablo(1 To 3, 1 To 2)    'tableau de 3 lignes et 2 colonne
    tablo(2, 1) = "toto"
    MsgBox UBound(tablo) & " ligne(s) et " & UBound(tablo, 2) & " colonne(s)" & vbCrLf & "tablo(2,1) =" & tablo(2, 1)
    t = redim_PreserveXY(tablo, 100, 5)
    MsgBox UBound(t) & " ligne(s) et " & UBound(t, 2) & " colonne(s)" & vbCrLf & "t(2,1) =" & t(2, 1)

End Sub



Function redim_PreserveXY(t, Optional lignes& = 0, Optional colonnes& = 0)
    Dim X, Y
    If lignes = 0 Then lignes = UBound(t)
    If colonnes = 0 Then colonnes = UBound(t, 2)
    X = Evaluate("ROW(1:" & lignes & ")")
    Y = Evaluate("COLUMN(" & Cells(1).Resize(, colonnes).Address(0, 0) & ")")
    redim_PreserveXY = Application.Index(t, X, Y)
End Function

DranReb je regarde ton dernier truc

 

[Dranreb]

Function Tbl(ByVal Rng As Range) As Variant()
   Dim RngAC As Range, TSor(), TEnt(), LMax As Long, CMax As Integer, L As Long, C As Integer
   Set RngAC = Application.Caller
   ReDim TSor(1 To RngAC.Rows.Count, 1 To RngAC.Columns.Count)
   TEnt = Rng.Value
   LMax = Borné(1, UBound(TEnt, 1), UBound(TSor, 1))
   CMax = Borné(1, UBound(TEnt, 2), UBound(TSor, 2))
   For L = 1 To LMax
      If L > LMax Then Exit For
      For C = 1 To CMax: TSor(L, C) = TEnt(L, C): Next C, L
   Tbl = TSor
   End Function
Private Function Borné(ByVal LimInf As Double, ByVal V As Double, ByVal LimSup As Double) As Double
   Borné = (LimInf + Abs(V - LimInf) - Abs(LimSup - V) + LimSup) / 2
   End Function

 

[patricktoulon]

re
Bon ben c'est un ref en cells(1) du caller et c'est tout
je vois pas trop comment l'utiliser en formule

de plus

Mais je ne comprends pas trop le problème. Si on récupère la valeur d'une plage trop grande ça ne génère pas de #N/A comme si on affecte un tableau à une plage trop grande. Et si on la retourne dans une plage matricielle trop petite il est simplement tronqué.

c'est justement l'inverse
le rng caller est plus grand que la plage argument de la fonction
mais ton idée du caller est bonne je l'avais oublié celui là

Function Tbl(Rng)
     Dim tbl2, t
    Tbl = Rng.Value
    'astuce 1 on cré un array (2 dimensions ( du meme nombre de lignes que la plage caller +1 pour etre sur et  1 colonne)) de nombres de 1  à 65535
    X = Evaluate("ROW(1:" & Application.Caller.Rows.Count + 1 & ")")
    ' je redimentionne MAINTENANT le tableau avec (l'astuce 1) pour les lignes et (array(1)) pour  1 colonne
    tbl2 = Application.Index(Tbl, X, Array(1))
    Tbl = tbl2
 Debug.Print Application.Caller.Rows.Count
  End Function

 

[patricktoulon]

@Dranreb on va pas faire une usine a gaz quand meme
surtout que la fonction réelle est deja plus importante que le .value de rng dans l'exemple de la discussion

 

[Dranreb]

Ce n'est pas encore une usine à gaz. Ma fonction Borné, que j'utilise beaucoup dans des tas de contextes évite simplement LMax = UBound(TEnt, 1): If LMax > UBound(TSor, 1) Then LMax = UBound(TSor, 1)

 

[Usine à gaz]

@Dranreb on va pas faire une usine a gaz quand meme
surtout que la fonction réelle est deja plus importante que le .value de rng dans l'exemple de la discussion

Bon jour à toutes et à tous
@patrick : raciste