Utiliser une plage nommée avec variable

Bonjour à tous,

Dans un même classeur, j'ai définis deux plages nommées avec le même nom :

[EntréeS] et [EntréeS]

Pour les différencier, j'ai ajouté une lettre selon le nom d'onglet dans laquelle chaque plage est nommée.

Dans l'onglet "B", la première plage est nommée [bEntréeS]

Dans l'onglet "M", la deuxième plage est nommée [mEntréeS]

Pour ne pas trop allonger le code pour les mêmes taches, j'ai utilisé une fonction commune

Mais le problème, je ne peux utiliser chaque plage de cette façon :

[x & "EntréeS]

Ça me déclenche une erreur !

Sub Stocks()
    Dim DerLig As Integer, lDerlig As Integer
    Dim N As String
    '-- Définition des plages nommées dans la feuille "BD"
    With Sheets("BD")
        DerLig = .Range("A" & Rows.Count).End(xlUp).Row

        .Range("A1:K" & DerLig).Name = "ZoneExtract"
        .Range("A2:A" & DerLig).Name = "sRéférence"
        .Range("C2:C" & DerLig).Name = "sEtat"
        .Range("D2:D" & DerLig).Name = "sDate"
        .Range("E2:E" & DerLig).Name = "sMouvement"
        .Range("F2:F" & DerLig).Name = "sQuantité"
    End With

    With Sheets("Listes")
        lDerlig = .Range("A" & Rows.Count).End(xlUp).Row
        .Range("A2:E" & lDerlig).Name = "TabRef"
        .Range("A2:A" & lDerlig).Name = "Ref"
    End With
    N = ActiveSheet.Name
    Call Stock(N)

    Application.Calculation = xlCalculationAutomatic
    Columns("A:I").EntireColumn.AutoFit
End Sub

Sub Stock(Nom As String)
    Dim sDerLig As Integer

    With Sheets(Nom)

        '-- Filtre
        .Activate
        [ZoneExtract].AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Range("A3:B3"), Unique:=True

        '-- Définition des plages nommées dans la& feuille "Nom"
        sDerLig = .Range("A" & Rows.Count).End(xlUp).Row
        x = LCase(Nom)
        .Range("D4:D" & sDerLig).Name = x & "EntréeS"
        .Range("E4:E" & sDerLig).Name = x & "SortieS"
        .Range("F4:F" & sDerLig).Name = x & "StockDispoS"
        .Range("C4:C" & sDerLig).Name = x & "StockInitialS"
        .Range("G4:G" & sDerLig).Name = x & "StockMiniS"
        .Range("H4:H" & sDerLig).Name = x & "DateS"
        .Range("I4:I" & sDerLig).Name = x & "AlerteS"

        '-- Ecriture des formules et calcul --'

        '-- Formule pour calculer la somme des entrées pour une référence
        .Range("D4").Formula = "=SUMPRODUCT((sRéférence=$A4)*(sEtat=""B"")*(sMouvement=""Entrée"")*(sQuantité))"
        .Range("D4").AutoFill Destination:=[x & EntréeS], Type:=xlFillDefault

        '-- Formule pour calculer la somme des sorties pour une référence
        .Range("E4").Formula = "=SUMPRODUCT((sRéférence=$A4)*(sEtat=""B"")*(sMouvement=""Sortie"")*(sQuantité))"
        .Range("E4").AutoFill Destination:=[x & SortieS], Type:=xlFillDefault

        '-- Formule pour calculer la quantité en stock pour une référence
        .Range("F4").Formula = "=C4+(D4-E4)"
        .Range("F4").AutoFill Destination:=[x & StockDispoS], Type:=xlFillDefault

        '---- Affichage du stock initial correspondant ---
        Range("C4").Formula = "=INDEX(TabRef,MATCH($A4,Ref,0),4)"
        Range("C4").AutoFill Destination:=[x & StockInitialS], Type:=xlFillDefault

        '---- Affichage du stock mini correspondant ---
        Range("G4").Formula = "=INDEX(TabRef,MATCH($A4,Ref,0),5)"
        Range("G4").AutoFill Destination:=[x & StockMiniS], Type:=xlFillDefault

        '-----

        '-- Calcul de la date du dernier mouvement
        .Range("H4").FormulaArray = "=MAX((sRéférence=$A4)*(sEtat=""Nom"")*sDate)"
        .Range("H4").AutoFill Destination:=[x & DateS], Type:=xlFillDefault
        [x & DateS].NumberFormat = "dd/mm/yyyy  hh:mm"

        '-- Derniere colonne
        .Range("I4").Formula = "=IF(x & StockMiniS="""","""",IF(x & StockDispoS < x & StockMiniS,""Attention: Stock bas"",""RAS""))"
        .Range("I4").AutoFill Destination:=[x & AlerteS], Type:=xlFillDefault
    End With
End Sub

Merci d'avance.

Bonjour,

ça me déclenche une erreur est bien vague... Quelle erreur ?

si tu débogues quelle ligne est surlignée.

A mon avis ça se passe quelque part par là :

Destination:=[x & EntréeS], ... et lignes suivantes.

Il faut écrire :

Destination:=[x & "EntréeS"]...

A tester.

A+

Bonsoir galopin01,

J'ai testé, mais j'ai reçu la même erreur :

Erreur d'exécution '1004':

La méthode AutoFill de la classe a échoué.

Sur la ligne suivante :

.Range("F4").AutoFill Destination:=[x & "EntréeS"], Type:=xlFillDefault

Tu codes dans quel Module ?

Ton "F4" est pas bon mais je pense que c'est une erreur de plume :

Essaie :

.Range("D4").AutoFill Destination:=.Range (x & "EntréeS"), Type:=xlFillDefault

A+

galopin01 a écrit :

Tu codes dans quel Module ?

Module3

Ton "F4" est pas bon mais je pense que c'est une erreur de plume :

Oui, effectivement

Essaie :

.Range("D4").AutoFill Destination:=.Range (x & "EntréeS"), Type:=xlFillDefault

A+

Ca a marché pour les plages utilisées dans un code VBA, mais pas pour les plages utilisées dans les formules.

parce que j'ai un probleme dans les colonnes H et I des onglets "B" et "M" dans le fichier joint.

Bonjour

Remplaces les lignes correspondantes

.Range("H4").FormulaArray = "=MAX((sRéférence=$A4)*(sEtat=""" & Nom & """)*sDate)"
.
.
.
.Range("I4").Formula = "=IF(" & x & "StockMiniS="""","""",IF(" & x & "StockDispoS <" & x & "StockMiniS,""Attention: Stock bas"",""RAS""))"

Bonjour Benzai64,

Merci bien.

Ca marche.