VBA - Création d'une fonction

[NeMoS]

Bonjour à tous, bonjour le forum.

J'ai créé un bout de code qui permet de faire une recherche dans une feuille d'un classeur.

Et en fait, j'aimerai faire cette recherche sur des feuilles différentes de mon classeur.

En écrivant le code, je me suis rendu compte qu'à part le nom de la feuille où s'effectue la recherche, le code est absolument identique.
Ma macro fonctionne parfaitement bien, mais elle est très longue et très répétitive.

J'aimerai donc alléger mon code en créant une fonction que j’appellerai suivant la feuille sur laquelle je fais la recherche.

Mais voilà, je ne sais pas vraiment faire une fonction, comment définir la variable et comment l'appeler dans la macro principale
J'ai regardé un peu les tutoriaux mais bon, voilà, j'ai pas vraiment réussi...

If Not Sheets("SEARCH ENGINE").Range("g" & i & ":J" & i).Find(True) Is Nothing Then
       valeur = Sheets("SEARCH ENGINE").Range("A" & i).Value
       Set r = Sheets("PRODUCT KNOWLEDGE").Rows(2).Find(valeur, , xlValues, xlWhole)
       If Not r Is Nothing Then col = r.Column
           If Sheets("SEARCH RESULTS").Columns(l).Value = "" Then
                            Sheets("PRODUCT KNOWLEDGE").Cells(2, col).Copy
                            Sheets("SEARCH RESULTS").Cells(2, l).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                            :=False, Transpose:=False
                            colnew = Sheets("SEARCH RESULTS").Cells(2, l).Column
                            
                        Else:
                            Sheets("SEARCH RESULTS").Cells(2, l + 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                            :=False, Transpose:=False
                            colnew = Sheets("SEARCH RESULTS").Cells(2, l + 1).Column
                            
            End If
            l = l + 1
            
           
       For j = 3 To fintableau
                If Sheets("SEARCH ENGINE").Range("G" & i).Value = True Then
                    If Sheets("PRODUCT KNOWLEDGE").Cells(j, col).Value = "1" Then
                        If Sheets("SEARCH RESULTS").Range("a" & k).Value = "" Then
                            Sheets("PRODUCT KNOWLEDGE").Range("A" & j & ":D" & j).Copy
                            Sheets("SEARCH RESULTS").Range("a" & k).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                            :=False, Transpose:=False
                            Sheets("SEARCH RESULTS").Cells(k, colnew).Value = "1"
                        Else:
                            Sheets("SEARCH RESULTS").Range("a" & k + 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                            :=False, Transpose:=False
                            Sheets("SEARCH RESULTS").Cells(k + 1, colnew).Value = "1"
                        End If
                        k = k + 1
                        
                    End If
                End If
         Next
End if

La seule chose qui change est le nom de la feuille qui dans cet exemple s'appelle "PRODUCT KNOWLEDGE".

Dans ce code, j'ai un moteur de recherche sur la feuille SEARCH ENGINE, suivant les critères rentrés je vais chercher mes info dans la feuille PRODUCT KNOWLEDGE et je les copie dans un tableau dans la feuille SEARCH RESULTS.
Ceci n'est qu'une partie du code mais c'est celle qui revient à l'identique si je veux faire une recherche dans une autre feuille.


Si vous pouviez m''aider que vous en serait très très reconnaissante !!!

Par avance merci

Gwen

 

[Hervé]

Re : VBA - Création d'une fonction

bonjour 🙂

3 petits exemples pour te montrer comment boucler sur des feuilles d'un classeur

Public Sub appel_feuille()
Dim item
Dim i As Byte

MsgBox ActiveSheet.Name 'donne le nom de la feuille à l'ecran

For i = 1 To Sheets.Count
    MsgBox Sheets(i).Name 'donne le nom de toutes les feuilles du classeur
Next i

For Each item In Array("feuil1", "feuil3")  'donne le nom des feuilles 1 et 3 seulement
     MsgBox Sheets(item).Name
Next item

End Sub

en esperant t'avoir aidé

a plus

 

[NeMoS]

Re : VBA - Création d'une fonction

Bonjour Hervé et merci pour ta réponse.

En fait ce n'est pas exactement ce que je recherche.

Je connais mes feuilles, en fait j'en ai trois. Dans mon moteur de recherche, je sélectionne des checkbox, et suivant les checkbox sélectionnées, je fais ma recherche sur l'une des trois feuille, voir même sur les trois, vu qu'il n'y a aucune restriction sur la sélection des checkbox.

Ma macro telle que je l'ai écrite se répète trois fois avec pour seule variable le nom de la feuille dans laquelle la recherche s'effectue.

Ce que je voulais savoir, c'est comment faire pour finalement n'écrire mon code qu'une seule fois et l'appeler suivant la feuille dans laquelle je fais la recherche.
Un truc du genre
For i=1 to 100 then
SEARCH(feuille1)
next i
For i=101 to 200 then
SEARCH(feuille2)
next

où SEARCH est une fonction (mais ce n'est peut être pas une fonction qu'il faut) où se trouve l'intégralité de mon code....

D'ailleurs, en écrivant ça, je me dis que mon i doit aussi être une variable de ma fonction...

Bref, j'ai essayé quelques syntaxes, mais sans succès

Encore merci !!

Gwen

 

[NeMoS]

Re : VBA - Création d'une fonction

Re,

En fait ce que je en sais pas faire c'est gérer toutes les variables qui sont dans la macro:
ici i, j, k, l, col, colnew.

Normalement, quand on arrive à l'exécution de ce bout de code, toutes ces variables ont été calculées et ont donc une valeur précise, mais comment je les gère dans la définition de la function ( si je crée une fonction) ?....

Gwen

 

[Hervé]

Re : VBA - Création d'une fonction

re

je ne pense pas que tu es besoin d'une fonction pour faire ce que tu veux.

une fonction permet de faire un calcul précis, pas une boucle sur des feuilles.

tu n'es pas loin de la vérité quand tu écris :

For i=1 to 100 then
SEARCH(feuille1)
next i

il te faut juste la bonne syntaxe pour boucler, je t'ai donné deux exemples de boucles dans mon précédent message les as tu testés ?

je te fais une boucle sur ta premiere partie de ton code, je n'ai pas tester et te laisse comprendre :

For x = 1 To 5 'pour boucler sur les 5 premieres feuilles du classeur
If Not Sheets("SEARCH ENGINE").Range("g" & i & ":J" & i).Find(True) Is Nothing Then
       valeur = Sheets("SEARCH ENGINE").Range("A" & i).Value
       Set r = Sheets(x).Rows(2).Find(valeur, , xlValues, xlWhole)
       If Not r Is Nothing Then col = r.Column
           If Sheets("SEARCH RESULTS").Columns(l).Value = "" Then
                            Sheets(x).Cells(2, col).Copy
                            Sheets("SEARCH RESULTS").Cells(2, l).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                            :=False, Transpose:=False
                            colnew = Sheets("SEARCH RESULTS").Cells(2, l).Column
                           
                        Else:
                            Sheets("SEARCH RESULTS").Cells(2, l + 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                            :=False, Transpose:=False
                            colnew = Sheets("SEARCH RESULTS").Cells(2, l + 1).Column
                           
            End If

Next x

ensuite il te faudra savoir sur quelles feuilles bouclées, apparemment ça se fait suivant un choix de checkbox.

on sait gérer cette événement, mais là il faut que tu fournisses un fichier

a plus

 

[NeMoS]

Re : VBA - Création d'une fonction

Bonjour Hervé,

Merci pour la réponse.
Je comprends bien mieux ce que tu veux dire avec les boucles sur les feuilles.

Je regarde comment je peux adapter aux contraintes de ma macro.

En fait, pour être simple, j'ai 10 checkbox : sur ces 10 checkbox, 8 renvoient à la feuille 3 , 1 renvoie à la feuille 4 et 1 renvoie à la feuille 5.

Je veux bien te transmettre mon fichier seulement si tu promets de ne pas hurler devant la lourdeur, longueur et répétitivité du code !!!! Parce qu'il n'y a pas que pour ce bout de code qu'il y énormément de répétitions...
Et je dois avouer que je ne suis pas très contente de ma macro, même si elle marche parfaitement bien...

En fait j'aimerais systématiser plein de bout de mon code !!!!!

Gwen

 

[Hervé]

Re : VBA - Création d'une fonction

salut

promis je ne hurlerai pas 🙄

et puis tu sais, je ne suis qu'un bidouilleur d'excel, de quel droit je hurlerai

a plus avec ton fichier

 

[NeMoS]

Re : VBA - Création d'une fonction

Salut !

Bon ben ça commence mal, je ne peux pas poster mon fichier...il est trop lourd (1.3M)

Comme quoi il faut VRAIMENT que j'allège mon code...ou alors ce sont les checkbox qui prennent trop de place, ce qui me semble plus logique...

Gwen