Excel de liste de la plage nommée dans une feuille de calcul et d'obtenir la valeur

Comment obtenir une liste de la plage nommée existent dans une feuille de calcul spécifique qui commencent par la chaîne donnée (par exemple tous les noms de plage de commencer avec au total) et de saisir la valeur? Je suis en train de faire Sous-Total et le Total du coût de l'hébergement basé sur la date. Je vais attribuer un nom unique pour chaque Sous-Total basé sur la Date de groupe. Ensuite, j'ai un bouton qui doivent être cliqué quand il a fini de calculer le Total, fondée sur la Plage Nommée que j'ai attribué de façon unique à chaque Sous-Total.

Ci-dessous le code que j'ai écrit pour faire le Grand Total:

Sub btnTotal()

    Dim Total, LastRowNo As Long

    LastRowNo = ActiveSheet.UsedRange.Row + ActiveSheet.UsedRange.Rows.Count

    Total = 0

    For Each N In ActiveWorkbook.Names
        Total = Total + IntFlight.Range(N.Name).Value
    Next N

    IntFlight.Range("$P" & LastRowNo).Select
    Selection.NumberFormat = "$* #,##0.00;$* (#,##0.00);$* ""-""??;@"
    With Selection
        .Font.Bold = True
    End With

    ActiveCell.FormulaR1C1 = Total

End Sub

Remarque: le IntFlight de "Total = Total + IntFlight.Gamme(N. Nom).Valeur" est le nom de ma feuille de calcul.

Le seul problème avec le code ci-dessus, il recherche tous les noms de gamme existe dans le classeur. J'ai juste besoin de trouver une plage nommée existent que dans une seule feuille de calcul, qui commencent avec la chaîne et le numéro de ligne (total26: désigne Sous-Total à partir de la ligne 26) et puis saisir la valeur à somme-ed comme le Grand Total.

Aucune idées de comment faire cela? Été passé 2 jours pour trouver la réponse.

Tas merci à l'avance.

EDIT 1 (Solution proposée par Charles Williams avec l'aide de bélisaire):

C'est ce que j'ai fait avec le code de Charles Williams:

Option Explicit
Option Compare Text

Sub btnIntFlightsGrandTotal()

    Dim Total, LastRowNo As Long
    LastRowNo = FindLastRowNo("International Flights")

    Dim oNM As Name
    Dim oSht As Worksheet
    Dim strStartString As String

    strStartString = "IntFlightsTotal"
    Set oSht = Worksheets("International Flights")

    For Each oNM In ActiveWorkbook.Names
        If oNM.Name Like strStartString & "*" Then
            If IsNameRefertoSheet(oSht, oNM) Then
                Total = Total + Worksheets("International Flights").Range(oNM.Name).Value
            End If
        End If
    Next oNM

    IntFlights.Range("$P" & LastRowNo).Select
    Selection.NumberFormat = "$* #,##0.00;$* (#,##0.00);$* ""-""??;@"
    With Selection
        .Font.Bold = True
    End With

    ActiveCell.FormulaR1C1 = Total

End Sub

Function FindLastRowNo(SheetName As String) As Long

    Dim oSheet As Worksheet
    Set oSheet = Worksheets(SheetName)

    FindLastRowNo = oSheet.UsedRange.Row + oSheet.UsedRange.Rows.Count

End Function

Merci à vous tous pour votre aide. Maintenant, j'ai besoin de venir avec ma propre version de ce script.

OriginalL'auteur Arief | 2010-10-27

  1. 4

    Voici un code qui vérifie si un Nom Défini commence par une chaîne de caractères et renvoie à une fourchette à l'intérieur de la plage utilisée d'une feuille de calcul du classeur et.

    Option Explicit
Option Compare Text
Sub FindNames()
    Dim oNM As Name
    Dim oSht As Worksheet
    Dim strStartString As String

    strStartString = "Total"
    Set oSht = Worksheets("TestSheet")

    For Each oNM In ActiveWorkbook.Names
        If oNM.Name Like strStartString & "*" Then
            If IsNameRefertoSheet(oSht, oNM) Then

                MsgBox oNM.Name
            End If
        End If
    Next oNM
End Sub

Function IsNameRefertoSheet(oSht As Worksheet, oNM As Name) As Boolean
    Dim oSheetRange As Range

    IsNameRefertoSheet = False
    On Error GoTo GoExit

    If Not oSht Is Nothing Then
        If Range(oNM.Name).Parent.Name = oSht.Name And _
           Range(oNM.Name).Parent.Parent.Name = oSht.Parent.Name Then
            Set oSheetRange = oSht.Range("A1").Resize(oSht.UsedRange.Row + oSht.UsedRange.Rows.Count - 1, oSht.UsedRange.Column + oSht.UsedRange.Columns.Count - 1)
            If Not Intersect(Range(oNM.Name), oSheetRange) Is Nothing Then IsNameRefertoSheet = True
            Set oSheetRange = Nothing
        End If
    End If

    Exit Function
GoExit:
End Function
    Semble détecter des plages nommées qui contient de la codé en dur (dans ce cas "A1") de la cellule . Suis-je la corriger? Je suppose que l'OP veut choisir librement où la gammes,sont,
    Non ce n'est pas correct: comme mentionné auparavant, le code ressemble à l'intérieur de la plage utilisée (ce qui signifie qu'il ignore les Noms avec refersto que les cellules de référence en dehors de la plage utilisée: vous pouvez facilement modifier le code si vous voulez changer cela). Dans le cas des OP cas, par définition, tous ses sous-totaux seront dans la usedrange.
    J'ai pensé la nuit dernière à faire la même comme ça. Trouver tous définis plage nommée qui commencent avec un total, prenez le RefersTo pour obtenir la valeur réelle faites ensuite le total Grand Total. Comme mon arrière-plan est fait dans le Réseau, c'est vraiment une bonne courbe d'apprentissage pour moi. Honnêtement, je ne comprends pas avec la Fonction IsNameRefertoSheet, en particulier sur une partie de la Gamme(lem.Nom).Parent.Nom du ou de la Plage(lem.Nom).Parent.Parent.Nom.
    Ce code fonctionne avec peu de modifications sur les Sous-Routine FindNames pour répondre à mes besoins (calculer le Total). En attendant, je suis encore à essayer de comprendre la Fonction IsNameRefertoSheet. Une fois que je peux obtenir autour de ma tête, je vais commencer par ma propre version de le faire. Peut-être Charles Williams ou quelqu'un veulent aider à le comprendre.
    Gamme(Lem.Nom) vous donne la gamme sur une feuille. Le parent de la gamme est la feuille, de sorte que le code vérifie si le nom de la feuille est le même que le nom de la mère de la plage (sa fiche). Le parent d'une feuille est son classeur de sorte que le code vérifie que sa le même classeur. L'erreur de manipulation prend soin de Noms qui ne sont pas de simples plages nommées (Formules, des constantes, des erreurs, etc). La plage utilisée qu'exclut les noms qui ont des références à l'extérieur les données sur la feuille. Cette fonction est plus généralisé que vous avez besoin, car il est pris à partir du Gestionnaire de noms de complément.

    OriginalL'auteur Charles Williams

  2. 0

    La fonction suivante affichera tous les noms et leurs totaux dans votre Classeur.

    Je pense que c'est le bloc de base vous avez besoin pour obtenir votre code en cours d'exécution. 

    Sub btnTotal()

    For Each N In ActiveWorkbook.Names
           MsgBox N.Name + " " + CStr(Application.WorksheetFunction.Sum(Range(N)))
    Next N
End Sub

    Modifier

    Pour répondre à votre commentaire:

    Définir vos noms de cette façon:

    Excel de liste de la plage nommée dans une feuille de calcul et d'obtenir la valeur

    Ensuite (et seulement ensuite) le code suivant fonctionne:

    Sub btnTotal()

  For Each N In ActiveSheet.Names
     If (InStr(N.Name, "!Total") <> 0) Then
         MsgBox N.Name + " " + CStr(Application.WorksheetFunction.Sum(Range(N)))
     End If
  Next N
End Sub

    Si vous n'avez pas de définir la portée des noms correctement, vous avez besoin de beaucoup de travail supplémentaire dans votre code.

    Modifier
    Comme vous avez oublié de mentionner que vous travaillez toujours avec Excel 2003, ici, vous trouverez un addin pour gérer le nom de la portée dans cette version. Voir capture d'écran ci-dessous

    Excel de liste de la plage nommée dans une feuille de calcul et d'obtenir la valeur

    HTH

    Votre code toujours à la recherche de tous les noms de plage dans un classeur. J'ai juste besoin de trouver tous les noms qui commencent avec Total, alors que le nombre dans une feuille de calcul spécifique, puis saisir la valeur calculée de la Grand Total.
    Un nom dans Excel peut couvrir plus d'une feuille de calcul. Il n'est pas pratique pour filtrer que dans le code. Nom de votre varie selon le nom de la feuille. Comme pour le filtrage de ceux qui commencent par "Total", vous pouvez le faire avec un SI sur N. Nom dans mon code.
    Désolé j'ai oublié de mentionner que je développe cette sous Excel 2003 où, je pense, a pas cette fonctionnalité.
    Ici, jkp-ads.com/officemarketplacenm-en.asp?AllComments=True vous avez un add-in qui vous offre la détermination de la portée de fonctionnalité pour Excel 2003.
    Merci pour l'info.

    OriginalL'auteur Dr. belisarius