Referring to Dynamic Named Ranges in VBA

Question

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.

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
– Tim Williams, Commented Aug 8, 2012 at 16:26
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
– Michael, Commented Aug 8, 2012 at 16:44

Dick Kusleika · Accepted Answer · 2015-06-25 12:43:13Z

6

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

Sum_Date refers to a sheet other than Summary. Check the RefersTo property of Sum_Date and make sure nothing is misspelled.
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.
There is an error in the RefersTo formula of Sum_Date. It sounds like you already verified that this isn't the case.

edited Jun 25, 2015 at 12:43

answered Aug 8, 2012 at 17:53

Dick Kusleika

33.3k4 gold badges56 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Michael Over a year ago

Ach, turns out it was an error in my RefersTo formula. I was so sure they were fine as I'd been using a macro to create them. Forgot I'd tweaked the macro slightly which introduced an error into my more recent named ranges. Thanks

Petak_CZ · Accepted Answer · 2021-03-18 19:59:28Z

2

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. :)

answered Mar 18, 2021 at 19:59

Petak_CZ

212 bronze badges

1 Comment

robotik Over a year ago

by dynamic named range I meant going through a list of names and do something with the range that is named like the name from the loop. with your code I can go like .Evaluate("Name" & cnt) where the cells are named like Name1 and Name2 ... , which is what I was just looking for

Joe Hilby · Accepted Answer · 2016-05-17 15:56:13Z

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

Roney Michael · Accepted Answer · 2014-01-25 16:59:37Z

-3

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!!!

edited Jan 25, 2014 at 16:59

Roney Michael

3,9945 gold badges32 silver badges45 bronze badges

answered Jan 25, 2014 at 16:39

user3235601

1

Collectives™ on Stack Overflow

Referring to Dynamic Named Ranges in VBA

4 Answers 4

1 Comment

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related