Referring to Dynamic Named Ranges in VBA

5
votes

I'm having troubling referring to a Dynamic Name Range in VBA.
My ranges are defined as

    =OFFSET(Sheet!$B$2,0,0,COUNTA(Sheet!$B:$B)-1,1)

My code should search one range for all entries in another range, the intention being that any missing entries will be added. So far I have

    Sub UpdateSummary()
    Dim Cell As Range
    Dim rngF As Range
    Set rngF = Nothing

    ' Step through each cell in data range
    For Each Cell In Worksheets("Aspect").Range("A_Date")
        ' search Summary range for current cell value
        Set rngF = Worksheets("Summary").Range("Sum_Date").Find(Cell.Value) // Does not work
        If rngF Is Nothing Then
            ' Add date to Summary
        End If
        Set rngF = Nothing
    Next Cell
    End Sub

The For loop seems to work ok. However, using the .Find method is giving me an error message.

    Application-defined or object-defined error

It does work if I replace the named range with a specific range ($B$2:$B$5000), so it seems to be down to how the named range is being passed.
Any ideas would be appreciated.

Thanks.

4
vbaexcelexcel-2010
Tr qualifying all your Range references with the actual worksheet. Any unqualified Range will refer to the active sheet, so you'll run into problems if the range you're referencing isn't on that sheet. – Tim Williams
Thanks, although it does not seem to help. I've updated my code above to reflect the changes, but the outcome is still the same. – Michael
What's the value of Cell when it fails? – Tim Williams

4 Answers

5
votes

The error is almost definitely because Excel can't find a named range Sum_Date that refers to a range on a worksheet named Summary. The most common causes are

  1. Sum_Date refers to a sheet other than Summary. Check the RefersTo property of Sum_Date and make sure nothing is misspelled.
  2. There is not a named range Sum_Date, that is, it's misspelled in the VBA code. Check the spelling of the named range in the Name Manager.
  3. There is an error in the RefersTo formula of Sum_Date. It sounds like you already verified that this isn't the case.
0
votes

I've had the a similar if not the same problem & here's how I solved it:

I first realized that the method I used to create my named range, using the Name Manager, my named range had a scope of Workbook. This is important because, it doesn't belong to the worksheet, & therefore will not be found there.

So, Worksheets("Summary").Range("Sum_Date") would not work for me.

Since my range belonged to the workbook, the way I was able to find is to use ActiveWorkbook.Names("Sum_Date")

For me I used it to remove the formula from named range that I am using in many places. The huge advantage is that named range is updated only once instead of the formula being called for every cell location that ranged is called. Huge time delay difference!

Public last_Selection As String

Private Sub Worksheet_Change(ByVal Target As Range)
'excel data change detection

If Range(last_Selection).Column = 2 Then
    'Disable events, so this only executes once
    Application.EnableEvents = False

    'This can be done with a complex formula in a cell, 
    'but this is easily understood
    Range("B1").End(xlDown).Select
    ActiveWorkbook.Names("last_Entry").Value = ActiveCell.Row

    'Re-enable so this routine will execute on the next change
    Application.EnableEvents = True
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    'constantly store the last cell to know which one was previously edited
    last_Selection = Target.Address
End Sub
0
votes

I know this is a very old thread, but I had the same issue today and I was looking for solution for quite a long time. So maybe this will help someone.

The named "range" defined by the =OFFSET(...) formula is actually a named FORMULA, so in VBA you have to evaluate it first to get the range. E.g.:

Set rgNamedRange = Worksheets("Summary").Evaluate("Sum_Date")

Credits to a guy named "shg" from mrexcel.com, who got me on right track. :)

-2
votes

I have been experimenting with this for a few days and eventually I came up with the following. It may not be the most efficient but it did work for me!

The named range of "OhDear" was set up in the normal way

Dim vItem As Variant
Set vItem = Names("OhDear")
Debug.Print vItem.Name

Worth a try don't you think! This does not work if instead of using a variant you use something like: Dim Nm as Name: Set Nm = Names("OhDear"). Any variations using 'Nm' failed!!!