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.
Answer
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.
No comments:
Post a Comment