views:

45

answers:

2

Hi there,

How to obtain a list of named range exist in a specific worksheet that start with particular string (for example all named range that start with total) and grab the value? I am trying to do Sub Total and Grand Total of accommodation cost based on the date. I will assign an unique name for each Sub Total based on the Date group. Then, I have a button that need to be clicked when it finishes to calculate the Grand Total based on the Named Range that I've assigned uniquely to each Sub Total.

Below is the code I wrote to do the 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

Note: the IntFlight from "Total = Total + IntFlight.Range(N.Name).Value" is the name of my worksheet.

The only problem with above code, it will looking all named range exist in the workbook. I just need to find named range exist in one particular worksheet, which start with given string and the row number (total26: means Sub Total from row 26) and then grab the value to be sum-ed as Grand Total.

Any ideas how to do this? Been spending 2 days to find the answer.

Thanks heaps in advance.

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

This is what I have done with the code from 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

Thank you all for your help. Now, I need to come up with my own version for this script.

A: 

The following function will output all the names and their totals in your Workbook.

I think it is the basic block you need to get your code running.

Sub btnTotal()

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

Edit

Answering your comment:

Define your names in this way:

alt text

Then (and only then) the following code works:

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

If you do not define the scope of the names correctly you need a lot of extra work in your code.

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

alt text

HTH

belisarius
Your code still looking for all named range in a workbook. I just need to find all named range which start with Total then whatever number in a specific worksheet, then grab the value to be calculated as Grand Total.
Arief
@Arief A name in Excel may cover more than one worksheet. It is not practical to filter that in code. Name your ranges according to the sheet name. As for filtering those that start with "Total", you can do it with an IF on N.Name in my code.
belisarius
@belisarius Sorry I forgot to mention that I develop this under Excel 2003 where I think has not have that feature.
Arief
@Arief Here http://www.jkp-ads.com/officemarketplacenm-en.asp?AllComments=True you have an addin that provides you with the scoping feature for Excel 2003.
belisarius
@belisarius Thank you for the info.
Arief
A: 

Here is some code that checks if a Defined Name starts with a string and refers to a range within the used range of a given worksheet and workbook.

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
Charles Williams
Seems to detect only named ranges that contains the hardcoded (in this case "A1") cell . Am I correct? I guess the OP wants to freely chose where the ranges are,
belisarius
No thats not correct: as mentioned the code looks within the used range (which means it ignores Names with refersto that reference cells outside the used range: you can easily modify the code if you want to change that). In the OP's case by definition all his subtotals will be within the usedrange.
Charles Williams
I have been thinking last night to do similar like this. Finding all defined named range that start with total, grab the RefersTo to get the actual value then total them as Grand Total. As my background is actually in the Networking, this is a really good learning curve for me. Honestly, I do not understand with the Function IsNameRefertoSheet, especially on part Range(oNM.Name).Parent.Name or Range(oNM.Name).Parent.Parent.Name.
Arief
This code is working with few modifications on Sub Routine FindNames to accommodate my needs (calculate the Grand Total). Meanwhile, I am still trying understand the Function IsNameRefertoSheet. Once I can get around my head, I will start with my own version for doing this. Perhaps Charles Williams or anyone want to help to understand it.
Arief
Range(ONm.Name) gives you the range on a sheet. The parent of the range is the sheet, so the code checks if the name of the sheet is the same as the name of the parent of the range (its sheet). The parent of a sheet is its workbook so the code checks that its the same workbook. The error handling takes care of Names that are not simple named ranges (Formulae, constants, errors etc). The used range check just excludes names that have references outside the data on the sheet. This function is more generalised than you need because it is taken from the Name Manager addin.
Charles Williams