Search code examples
vbaexcelexcel-2010

Referring to Dynamic Named Ranges in VBA

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.

Solution

  • 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.