Excel перечисляет именованный диапазон в листе и получает значение

Как получить список именованного диапазона существует на конкретном листе, который начинается с конкретной строки (например, весь именованный диапазон, который начинается с total) и захватывает значение? Я пытаюсь сделать Sub Total и Grand Total стоимости проживания на основе даты. Я назначу уникальное имя для каждого Sub Total на основе группы Date. Затем у меня есть кнопка, которую нужно щелкнуть, когда она закончится, чтобы вычислить Grand Total на основе Именованного диапазона, который я присвоил уникально каждому Sub Total.

Ниже приведен код, который я написал для создания 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 

Примечание. IntFlight из «Total = Total + IntFlight.Range (N.Name) .Value» – это имя моего рабочего листа.

Единственная проблема с вышеуказанным кодом – это поиск всех названных диапазонов в книге. Мне просто нужно найти именованный диапазон в одном конкретном листе, который начинается с заданной строки и номера строки (total26: означает Sub Total из строки 26), а затем захватить значение, которое будет суммироваться как Grand Total.

Есть идеи, как это сделать? Проводили 2 дня, чтобы найти ответ.

Спасибо за кучи заранее.

РЕДАКТИРОВАТЬ 1 (Решение, предоставленное Чарльзом Уильямсом с помощью вероисповедания):

Это то, что я сделал с кодом Чарльза Уильямса:

 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 

Спасибо за вашу помощь. Теперь мне нужно придумать свою версию для этого скрипта.

    Ниже приведен код, который проверяет, начинается ли имя Defined Name со строкой и относится к диапазону в пределах используемого диапазона данного рабочего листа и рабочей книги.

     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 

    Следующая функция выведет все имена и их итоги в вашей книге.

    Я думаю, что это основной блок, необходимый для запуска вашего кода.

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

    редактировать

    Отвечая на ваш комментарий:

    Определите свои имена таким образом:

    alt text

    Затем (и только тогда) работает следующий код:

     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 

    Если вы не определяете область имен правильно, вам нужно много дополнительной работы в вашем коде.

    Изменить Как вы забыли упомянуть, что вы все еще работаете с Excel 2003, здесь вы найдете addin для управления областью имен в этой версии. См. Экранную крышку ниже

    alt text

    НТН

    Давайте будем гением компьютера.