在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
- Sum_Date指摘要以外的工作表.检查Sum_Date的RefersTo属性,并确保没有拼写错误.
- 没有命名范围Sum_Date,即VBA代码中的拼写错误.在名称管理器中检查命名范围的拼写.
- Sum_Date的RefersTo公式中有错误.听起来您已经验证了事实并非如此.
这篇关于在VBA中引用动态命名范围的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!