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​
 

job75

XLDnaute Barbatruc
Comme Yeahou en a parlé voici une solution avec une matrice (Array) en base 1 comme argument :
VB:
Function Matrice(Source)
'Source est une plage de cellules ou une matrice en base 1
Dim nlig&, ncol%, tablo, i&, j%
Source = Source
If IsArray(Source) Then nlig = UBound(Source): ncol = UBound(Source, 2) Else nlig = 1: ncol = 1
ReDim tablo(1 To Application.Caller.Rows.Count, 1 To ncol)
For i = 1 To UBound(tablo)
    For j = 1 To ncol
        If i > nlig Then tablo(i, j) = "" Else If IsArray(Source) Then tablo(i, j) = Source(i, j) Else tablo(1, 1) = Source
Next j, i
Matrice = tablo
End Function
Ici Application.Caller ne crée pas de référence circulaire.
 

Pièces jointes

  • Matrice(4).xlsm
    20.4 KB · Affichages: 0

patricktoulon

XLDnaute Barbatruc
re
@job75
VB:
'formule   " =matrice(A1:A15)"

Sub testmatrice()
  X = Matrice([A1:A15].Value)
[F1].Resize(UBound(X)) = X
End Sub

Function Matrice(Source)
'Source est une plage de cellules ou une matrice en base 1
Dim nlig&, ncol%, tablo, i&, j%
Source = Source
If IsArray(Source) Then nlig = UBound(Source): ncol = UBound(Source, 2) Else nlig = 1: ncol = 1
On Error Resume Next
ReDim tablo(1 To Application.Caller.Rows.Count, 1 To ncol)
If Err.Number > 0 Then ReDim tablo(1 To nlig, 1 To ncol)
For i = 1 To UBound(tablo)
    For j = 1 To ncol
        If i > nlig Then tablo(i, j) = "" Else If IsArray(Source) Then tablo(i, j) = Source(i, j) Else tablo(1, 1) = Source
Next j, i
Matrice = tablo
End Function
 

job75

XLDnaute Barbatruc
J'ai constaté (avec DebugPrint) que la fonction se calcule 2 fois quand on la valide.

Pour l'éviter il faut déclarer l'argument ByRef, fichier (5) :
VB:
Function Matrice(ByRef Source As Variant)
'Source est une plage de cellules ou une matrice en base 1
Debug.Print 1
Dim nlig&, ncol%, tablo, i&, j%
Source = Source
If IsArray(Source) Then nlig = UBound(Source): ncol = UBound(Source, 2) Else nlig = 1: ncol = 1
ReDim tablo(1 To Application.Caller.Rows.Count, 1 To ncol)
For i = 1 To UBound(tablo)
    For j = 1 To ncol
        If i > nlig Then tablo(i, j) = "" Else If IsArray(Source) Then tablo(i, j) = Source(i, j) Else tablo(1, 1) = Source
Next j, i
Matrice = tablo
End Function
 

Pièces jointes

  • Matrice(5).xlsm
    20.6 KB · Affichages: 2

patricktoulon

XLDnaute Barbatruc
rre
oui mais si on peut ajouter une ligne pour s'en servir en vba pourquoi se priver

et en effet dans le debug celle basée sur mapomme Calcul 2 fois la mienne 1 fois
et la tienne avec la gestion d'erreur 6 fois
mais j'ai compris la chose
c'est une matriciel donc elle se repete en cas d' erreur
pour palier au repeat je change la gestion d'erreur
je ne fait pas un resume next et goto 0 mai un goto err
et err : on error goto 0
en fait on sort pas du flux
on apprend des trucs aujourd'hui avec mon bidule ;)
démonstration
demo2.gif


VB:
Function Matricex(ByRef Source As Variant)
'Source est une plage de cellules ou une matrice en base 1
Debug.Print 1
Dim nlig&, ncol%, tablo, i&, j%
Source = Source: tablo = Source
If IsArray(Source) Then nlig = UBound(Source): ncol = UBound(Source, 2) Else nlig = 1: ncol = 1
On Error Resume Next
'On Error GoTo err
ReDim tablo(1 To Application.Caller.Rows.Count, 1 To ncol)
err: On Error GoTo 0
'On Error GoTo 0
For i = 1 To UBound(tablo)
    For j = 1 To ncol
        If i > nlig Then tablo(i, j) = "" Else If IsArray(Source) Then tablo(i, j) = Source(i, j) Else tablo(1, 1) = Source
Next j, i
Matricex = tablo
End Function 'formule   " =matrice(A1:A15)"

Sub testmatricex()
  X = Matricex([A1:A15].Value)
[F1].Resize(UBound(X)) = X
End Sub
 

job75

XLDnaute Barbatruc
@patricktoulon tu parles d'une procédure Sub pour appeler la fonction.

Mais alors la fonction est inutile et il n'y a pas besoin d'effacer des #N/A.

Dans le fichier joint la plage source est copiée et collée à la demande :
VB:
Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Application.EnableEvents = False 'désactive les évènements
[H2].Resize(Rows.Count - 1, Columns.Count - 7).Delete 'RAZ
If Int(Val([F1])) < 1 Or Int(Val([F2])) < 1 Then Exit Sub
[A2].Resize(Int(Val([F1])), Int(Val([F2]))).Copy [H2]
Application.EnableEvents = True 'réactive les évènements
End Sub
 

Pièces jointes

  • Copier(1).xlsm
    17 KB · Affichages: 1

Statistiques des forums

Discussions
313 329
Messages
2 097 235
Membres
106 883
dernier inscrit
Papalo