XL 2016 conversion de fonction à macro

Jacky67 à dit:

Bonjour,
Ceci peut-être..

VB:

Sub testJJ()
    With Feuil4.[b11:l27]
        .Formula = "=INDEX(BARESULTAT!$C$2:$S$500,MATCH(B$10,BARESULTAT!$A$2:$A$500,0),MATCH($A11,BARESULTAT!$C$1:$S$1,0))"
        .Value = .Value
    End With
End Sub

Cliquez pour agrandir...

Function TableItem ( Table , dansPremCol , dansPremLig , SiAbsent , PresentMaisVide )

Cliquez pour agrandir...

• Table est la plage du tableau dans lequel on va rechercher un élément
• dansPremcol est la valeur recherchée dans la première colonne de Table
• dansPremligne est la valeur recherchée dans la première colonne de Table
• SiAbsent est la valeur renvoyée si au moins une des deux valeurs cherchées est absente
• PresentMaisVide est la valeur renvoyée si l'élément a été trouvé et s'il est vide

=TableItem ( BARESULTAT!$A$1:$T$144 ; B$10 ; $A11 ; 0 ; "" )

Cliquez pour agrandir...

Function TableItem(Table As Range, ByVal dansPremCol, ByVal dansPremLig, ByVal SiAbsent, ByVal PresentMaisVide)
Dim t, i&, j&, y
   t = Table
   For i = 1 To UBound(t)
      If dansPremCol = t(i, 1) Then Exit For
   Next i
   If i >= UBound(t) Then TableItem = SiAbsent: Exit Function
   For j = 1 To UBound(t, 2)
      If dansPremLig = t(1, j) Then Exit For
   Next j
   If j >= UBound(t, 2) Then TableItem = SiAbsent: Exit Function
   y = Table(i, j)
   If y = "" Then TableItem = PresentMaisVide Else TableItem = y
End Function

• Première méthode : 50 000 000 de valeurs à stoker en mémoire
• Deuxième méthode : on stocke d'abord 50 000 valeurs en mémoire remplacées ensuite par 1000 valeurs en mémoire soit 50 000 valeurs au maximum.

Function TableItem(Table As Range, ByVal dansPremCol, ByVal dansPremLig, ByVal SiAbsent, ByVal PresentMaisVide)
'recherche le rang i de la valeur "dansPremCol" au sein de la 1ère colonne du tableau Table à partir de 2ème élément.
'recherche le rang j de la valeur "dansPremLig" au sein de la 1ère ligne du tableau Table à partir de 2ème élément.
'Si au moins une des 2 valeurs (i, j) n'est pas trouvée alors on retourne la valeur "SiAbsent"
'Sinon on retient la valeur y à l'intersection de la ligne i et de la colonne j
'NOTA: si y est vide alors on retourne la valeur "PresentMaisVide" sinon on retourne y
Dim t, i&, j&, y
   t = Table.Columns(1)
   For i = 1 To UBound(t)
      If dansPremCol = t(i, 1) Then Exit For
   Next i
   If i >= UBound(t) Then TableItem = SiAbsent: Exit Function
   t = Table.Rows(1)
   For j = 1 To UBound(t, 2)
      If dansPremLig = t(1, j) Then Exit For
   Next j
   If j >= UBound(t, 2) Then TableItem = SiAbsent: Exit Function
   y = Table.Cells(i, j)
   If y = "" Then TableItem = PresentMaisVide Else TableItem = y
End Function

mapomme à dit:

Re,
Salut @Staple1600

La même mais beaucoup moins gourmande en ressource mémoire. On ne stocke plus en mémoire vive le tableau dans sa totalité mais uniquement sa 1ère colonne puis sa 1ère ligne.

Exemple : un tableau de 1 000 colonnes et 50 000 lignes.

• Première méthode : 50 000 000 de valeurs à stoker en mémoire
• Deuxième méthode : on stocke d'abord 50 000 valeurs en mémoire remplacées ensuite par 1000 valeurs en mémoire soit 50 000 valeurs au maximum.

VB:

Function TableItem(Table As Range, ByVal dansPremCol, ByVal dansPremLig, ByVal SiAbsent, ByVal PresentMaisVide)
'recherche le rang i de la valeur "dansPremCol" au sein de la 1ère colonne du tableau Table à partir de 2ème élément.
'recherche le rang j de la valeur "dansPremLig" au sein de la 1ère ligne du tableau Table à partir de 2ème élément.
'Si au moins une des 2 valeurs (i, j) n'est pas trouvée alors on retourne la valeur "SiAbsent"
'Sinon on retient la valeur y à l'intersection de la ligne i et de la colonne j
'NOTA: si y est vide alors on retourne la valeur "PresentMaisVide" sinon on retourne y
Dim t, i&, j&, y
   t = Table.Columns(1)
   For i = 1 To UBound(t)
      If dansPremCol = t(i, 1) Then Exit For
   Next i
   If i >= UBound(t) Then TableItem = SiAbsent: Exit Function
   t = Table.Rows(1)
   For j = 1 To UBound(t, 2)
      If dansPremLig = t(1, j) Then Exit For
   Next j
   If j >= UBound(t, 2) Then TableItem = SiAbsent: Exit Function
   y = Table.Cells(i, j)
   If y = "" Then TableItem = PresentMaisVide Else TableItem = y
End Function

Cliquez pour agrandir...

mapomme à dit:

Bonjour @zozo , @Jacky67

Voici une fonction personnalisée plus générique:

• Table est la plage du tableau dans lequel on va rechercher un élément
• dansPremcol est la valeur recherchée dans la première colonne de Table
• dansPremligne est la valeur recherchée dans la première colonne de Table
• SiAbsent est la valeur renvoyée si au moins une des deux valeurs cherchées est absente
• PresentMaisVide est la valeur renvoyée si l'élément a été trouvé et s'il est vide

Exemple d'utilisation en cellule b11 de la feuille "CHGE-DIRECT":

On retourne 0 si on n'a pas pas trouvé une des deux valeurs B$10 ou $A11.
On retourne "" si les deux valeurs ont été trouvé mais si la valeur du tableau est 'vide'.

Pour l'exemple, la cellule C24 dans le tableau a été mise à vide (au lieu de la formule =somme(...))

Code de la fonction dans Module1:

VB:

Function TableItem(Table As Range, ByVal dansPremCol, ByVal dansPremLig, ByVal SiAbsent, ByVal PresentMaisVide)
Dim t, i&, j&, y
   t = Table
   For i = 1 To UBound(t)
      If dansPremCol = t(i, 1) Then Exit For
   Next i
   If i >= UBound(t) Then TableItem = SiAbsent: Exit Function
   For j = 1 To UBound(t, 2)
      If dansPremLig = t(1, j) Then Exit For
   Next j
   If j >= UBound(t, 2) Then TableItem = SiAbsent: Exit Function
   y = Table(i, j)
   If y = "" Then TableItem = PresentMaisVide Else TableItem = y
End Function

Cliquez pour agrandir...