Question

如何获取以特定字符串开头的特定工作表中存在的命名范围的列表（例如，以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

注意：“Total=Total+IntFlight.Range（N.Name）.Value”中的IntFlight是我工作表的名称。

上面代码的唯一问题是，它将查找工作簿中存在的所有命名区域。我只需要找到一个特定工作表中存在的命名范围，它以给定的字符串和行号开始（total26：表示第26行的小计），然后获取要求和的值作为总计。

有什么办法吗？花了两天时间才找到答案。

提前表示感谢。

EDIT 1 (Solution Provided by Charles Williams with help from belisarius):

以下是我对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

谢谢大家的帮助。现在，我需要为这个脚本想出我自己的版本。

Answer 1

以下是一些代码，用于检查“定义名称”是否以字符串开头并引用给定工作表和工作簿的使用范围内的某个范围。

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

Answer 2

以下函数将输出工作簿中的所有名称及其总计。

我认为这是让代码运行所需的基本块。

Sub btnTotal()

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

编辑

回答您的评论：

用这种方式定义你的名字：

然后（并且只有到那时）以下代码才能工作：

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

如果您没有正确定义名称的范围，那么您需要在代码中做大量额外的工作。

Edit As you forgot to mention that you are still working with Excel 2003, here you will find an addin to manage name scoping in that version. See screen cap below

邻苯二甲酸

EDIT 1 (Solution Provided by Charles Williams with help from belisarius):

友情链接