Microsoft 365 Insertion d'une fonction avec variable dans une macro

bioteau · 19 Janvier 2022

Bonjour,
Je cherche à copier sur plusieurs onglets via vba une formule "fonction" qui fait référence à une partie de cellule qui est sur un onglet dont le nom est variable
Pour être plus précis, cette formule doit copier dans la cellule O2 une partie du nom de la cellule B1 (la cellule moins les 33 derniers caractères) de la feuille Fournisseur X +1 (X étant variable), j'ai différents onglets nommés FOURNISSEUR 1, FOURNISSEUR 2, FOURNISSEUR 3.....
Voici mon code :

Range("O2").FormulaR1C1 = "=LEFT('FOURNISSEUR '&i+1!R[-1]C[-13],LEN('FOURNISSEUR '&i+1!R[-1]C[-13])-33)"

Cela ne fonctionne pas, j'ai essayé de bouger le ', changer les espaces... Comment mettre la variable onglet.

Merci pour votre aide

bioteau · 20 Janvier 2022

sylvanu à dit:

Bonsoir,
Un essai en PJ avec :

VB:

Sub MiseAjour()
    Dim Ligne%, Colonne%, L%, F, Chaine$, Qté, Fournisseur
    Range("N1:ZZ100").ClearContents
    Application.ScreenUpdating = False
    ' Mise à jour Liste client ( colonne N )
    Ligne = 3
    For L = 3 To Range("B65500").End(xlUp).Row
        If Cells(L, "B") <> "" Then
            Cells(Ligne, "N") = Cells(L, "B"): Ligne = Ligne + 1
        End If
    Next L
    ' Mise à jour Liste fournisseurs
    Colonne = 15 ' Colonne O
    For Each F In Worksheets
        If Left(F.Name, 11) = "FOURNISSEUR" Then
            Chaine = Sheets(F.Name).[B1]
            Cells(1, Colonne) = F.Name
            Cells(2, Colonne) = Left(Chaine, Len(Chaine) - 33)
            Colonne = Colonne + 1...

chris · 19 Janvier 2022

Bonjour

Une fonction personnalisée doit se référer à la cellule ou plage passée en paramètre donc pas O2

Donc du style suivant pour une cellule

VB:

Function ReduireFour(Fournisseur)

    if Fournisseur="" then ReduireFour=""

    ReduireFour=Left(Fournisseur....

End Function

sylvanu · 19 Janvier 2022

Bonjour Bioteau, Chris,
En fait il faut reconstituer une chaine de caractères, donc le "i" ne doit pas être dans les guillemets. Essayez :

Code:

Range("O2").FormulaR1C1 = "=LEFT('FOURNISSEUR " & i + 1 & "'!R[-1]C[-13],LEN('FOURNISSEUR " & i + 1 & "'!R[-1]C[-13])-33)"

Attention au ' qui doit être après le i+1 et non avant.
J'ai testé, ça à l'air de marcher.

bioteau · 19 Janvier 2022

sylvanu à dit:
Bonjour Bioteau, Chris,
En fait il faut reconstituer une chaine de caractères, donc le "i" ne doit pas être dans les guillemets. Essayez :

Code:

Range("O2").FormulaR1C1 = "=LEFT('FOURNISSEUR " & i + 1 & "'!R[-1]C[-13],LEN('FOURNISSEUR " & i + 1 & "'!R[-1]C[-13])-33)"

Attention au ' qui doit être après le i+1 et non avant.
J'ai testé, ça à l'air de marcher.

Merci Sylvanu, cela fonctionne parfaitement.

bioteau · 19 Janvier 2022

La formule de Sylvanu fonctionne parfaitement mais je me heurte à un autre problème.
Comme je le disais j'ai X onglets Fournisseurs , nombre variable créés en amont par macro.
Je veux copier la formule de Sylvanu sur X colonnes (X correpondant au nombre de Fournisseurs) donc en fait mettre en en-tête de colonne tous les noms des fournisseurs qui se situent dans la même cellule sur les différents onglet Fournisseur X.
J'ai essayé avec la fonction INDIRECT mais j'ai sans cesse un message d'erreur.
Merci

sylvanu · 19 Janvier 2022

Ce serait possible d'avoir un fichier test ?
Ca éviterait de coder en aveugle.
En PJ d'après ce que j'ai compris, avec :

VB:

Sub Essai()
    For C = 15 To 17    ' 15 est la première colonne, 17 la dernière
        NomFeuille = Cells(1, C)
        Cells(2, C).Formula = "=LEFT('" & NomFeuille & "'!$B$1,LEN('" & NomFeuille & "'!$B$1)-33)"
    Next C
End Sub

La ligne 1 contient le nom des fournisseurs, qui doit être le même que le nom de l'onglet correspondant.

bioteau · 19 Janvier 2022

Voilà un fichier test, j'ai modifié celui que tu m'as envoyé car il ne correspond pas à mon cas.
J'ai indiqué là ou je voulais aboutir.
Les données doivent être indiquées sur chaque feuille Fournisseur, le but est de retrouver les fournisseurs communs des clients pour ensuite inscrire dans la colonne "Remarques" le nom du ou des fournisseurs.

sylvanu · 19 Janvier 2022

Bonsoir,
Un essai en PJ avec :

VB:

Sub MiseAjour()
    Dim Ligne%, Colonne%, L%, F, Chaine$, Qté, Fournisseur
    Range("N1:ZZ100").ClearContents
    Application.ScreenUpdating = False
    ' Mise à jour Liste client ( colonne N )
    Ligne = 3
    For L = 3 To Range("B65500").End(xlUp).Row
        If Cells(L, "B") <> "" Then
            Cells(Ligne, "N") = Cells(L, "B"): Ligne = Ligne + 1
        End If
    Next L
    ' Mise à jour Liste fournisseurs
    Colonne = 15 ' Colonne O
    For Each F In Worksheets
        If Left(F.Name, 11) = "FOURNISSEUR" Then
            Chaine = Sheets(F.Name).[B1]
            Cells(1, Colonne) = F.Name
            Cells(2, Colonne) = Left(Chaine, Len(Chaine) - 33)
            Colonne = Colonne + 1
        End If
    Next F
    ' Mise à jour relation client fournisseur
    Ligne = 3: Colonne = 15
    While Cells(2, Colonne) <> ""
        While Cells(Ligne, "N") <> ""
            Fournisseur = Cells(1, Colonne)
            Qté = Application.CountIf(Sheets(Fournisseur).[B:B], Cells(Ligne, "N"))
            If Qté <> 0 Then
                Cells(Ligne, Colonne) = Qté
            End If
            Ligne = Ligne + 1
        Wend
        Colonne = Colonne + 1: Ligne = 3 ' Réinit ligne
    Wend
    Application.ScreenUpdating = True
End Sub

Je me suis réservé la ligne 1 du tableau pour y mettre le nom de l'onglet, ce qui simplifie grandement les choses.

bioteau · 20 Janvier 2022

Un grand merci Sylvanu, tu me fais avancer dans mon projet et je vais analyser ce code pour comprendre et améliorer mon apprentissage en VBA.

bioteau · 20 Janvier 2022

sylvanu à dit:

Bonsoir,
Un essai en PJ avec :

VB:

Sub MiseAjour()
    Dim Ligne%, Colonne%, L%, F, Chaine$, Qté, Fournisseur
    Range("N1:ZZ100").ClearContents
    Application.ScreenUpdating = False
    ' Mise à jour Liste client ( colonne N )
    Ligne = 3
    For L = 3 To Range("B65500").End(xlUp).Row
        If Cells(L, "B") <> "" Then
            Cells(Ligne, "N") = Cells(L, "B"): Ligne = Ligne + 1
        End If
    Next L
    ' Mise à jour Liste fournisseurs
    Colonne = 15 ' Colonne O
    For Each F In Worksheets
        If Left(F.Name, 11) = "FOURNISSEUR" Then
            Chaine = Sheets(F.Name).[B1]
            Cells(1, Colonne) = F.Name
            Cells(2, Colonne) = Left(Chaine, Len(Chaine) - 33)
            Colonne = Colonne + 1
        End If
    Next F
    ' Mise à jour relation client fournisseur
    Ligne = 3: Colonne = 15
    While Cells(2, Colonne) <> ""
        While Cells(Ligne, "N") <> ""
            Fournisseur = Cells(1, Colonne)
            Qté = Application.CountIf(Sheets(Fournisseur).[B:B], Cells(Ligne, "N"))
            If Qté <> 0 Then
                Cells(Ligne, Colonne) = Qté
            End If
            Ligne = Ligne + 1
        Wend
        Colonne = Colonne + 1: Ligne = 3 ' Réinit ligne
    Wend
    Application.ScreenUpdating = True
End Sub

Je me suis réservé la ligne 1 du tableau pour y mettre le nom de l'onglet, ce qui simplifie grandement les choses.

LA SOLUTION : Merci

sylvanu · 20 Janvier 2022

Bonjour,
Vous pouvez aussi utiliser cette variante qui peut être intéressante.
Dans chaque feuille Fournisseur vous mettez ces trois lignes de code :

VB:

Sub Worksheet_Activate()
    MiseAjour
End Sub

A chaque fois que vous sélectionnez une de ces feuilles, la mise à jour est automatique.
Plus conviviale, cela va dépendre de sa vitesse d'exécution. Avec un fichier aussi simple la reconstruction de la table est invisible.

bioteau · 20 Janvier 2022

Merci Sylvanu, je ne vais pas avoir besoin de ce code pour cette fois car le code que tu m'as donné est intégré à une autre partie. Pour être précis, les feuilles sur lesquelles sont exécutées le code sont des feuilles temporaires qui servent à renseigner une plus grande base utile à la préparation de commande et préparation logistique. En partant d'un Tableau d'ordres de commandes, en une seule fois et de façon automatique, je chercher à obtenir les Bons de commandes et les Bons logistiques.
Je suis à 95% du projet et j'espère que les 5% restant ne vont pas remettre en question le travail réalisé jusqu'à présent.
Un grand merci à toi et tous ces passionnés du VBA qui nous font apprendre et avancer.

Microsoft 365 Insertion d'une fonction avec variable dans une macro

bioteau

XLDnaute Nouveau

bioteau

chris

XLDnaute Barbatruc

sylvanu

XLDnaute Barbatruc

bioteau

XLDnaute Nouveau

bioteau

XLDnaute Nouveau

sylvanu

XLDnaute Barbatruc

Pièces jointes

bioteau

XLDnaute Nouveau

Pièces jointes

sylvanu

XLDnaute Barbatruc

Pièces jointes

bioteau

XLDnaute Nouveau

bioteau

XLDnaute Nouveau

sylvanu

XLDnaute Barbatruc

Pièces jointes

bioteau

XLDnaute Nouveau

Discussions similaires