在VBA中引用动态命名范围 [英] Referring to Dynamic Named Ranges in VBA

问题描述

我在引用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 

For循环似乎可以正常工作.但是，使用.Find方法会给我一条错误消息.

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

    Application-defined or object-defined error

如果我将命名范围替换为特定范围($ B $ 2:$ B $ 5000)，它确实可以工作，所以这似乎取决于命名范围的传递方式.
任何想法将不胜感激.

谢谢.

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.

推荐答案

该错误几乎可以肯定是因为Excel找不到在名为Summary的工作表上引用一个范围的命名范围Sum_Date.最常见的原因是

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指摘要以外的工作表.检查Sum_Date的RefersTo属性，并确保没有拼写错误.
2. 没有命名范围Sum_Date，即VBA代码中的拼写错误.在名称管理器中检查命名范围的拼写.
3. Sum_Date的RefersTo公式中有错误.听起来您已经验证了事实并非如此.

这篇关于在VBA中引用动态命名范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！