在引用ListObjects表的VBA中创建动态命名范围 [英] Create dynamic named range in VBA that references ListObjects table
问题描述
我需要创建一个引用ListObject表中最后几行数据的命名范围。虽然我可以从功能区手动执行(公式>定义名称),我必须这样做20张,每个3个范围。
I need to create a named range that refers to the last few rows of data in a ListObject table. While I can do it manually from the ribbon (Formulas > Define Name) I have to do this across 20 sheets with 3 ranges each.
当我尝试使用VBA I得到错误1004,并警告该公式有问题,并删除=,如果我不想输入一个公式。任何想法如何解决这个问题?
When I try this using VBA I get error 1004 and a warning that there's a problem with the formula and to remove the "=" if I'm not trying to enter a formula. Any ideas how to resolve this?
打开一个新的Sheet1来尝试我的代码。它将创建一个ListObjects表,并尝试创建命名范围。
Open a fresh Sheet1 to try my code. It'll create a ListObjects table and will try to create the Named range.
Sub test2()
Dim wks As Worksheet, tbl As ListObject
Dim arr()
arr = [{"Date","1/1/2016","2/1/2016","3/1/2016","4/1/2016";"Green",100,200,300,400;"Yellow",350,250,150,50;"Red",10,7,5,3}]
Set wks = ActiveSheet
wks.Range("a1:d5") = WorksheetFunction.Transpose(arr)
Set tbl = wks.ListObjects.Add(xlSrcRange, wks.Range("a1").CurrentRegion, , xlYes)
tbl.Name = "tblTix"
ActiveWorkbook.Names.Add "rngRedLast3Mos", "=OFFSET(tblTix,MATCH(MAX(tblTix[Date]),tblTix[Date],1)-3,MATCH(""Red"",tblTix[#Headers],0)-1,3,1)" 'THIS FAILS
ActiveWorkbook.Names.Add "rngRedLast3Mos", "=OFFSET(Sheet1!$A$1,2,3,3,1)" 'THIS WORKS
ActiveWorkbook.Names.Add "rngRedLast3Mos", "=Sheet1!$D$3:$D$5" 'THIS ALSO WORKS
Debug.Print ActiveWorkbook.Names("rngRedLast3Mos").RefersTo
Debug.Print ActiveSheet.Range("rngRedLast3Mos").Address
End Sub
如果我在名称管理器中手动定义范围,我可以分配这个引用,并且它可以正常工作:
If I manually define the range in Name Manager, I can assign this reference, and it works:
=OFFSET(tblTix,MATCH(MAX(tblTix[Date]),tblTix[Date],1)-3,MATCH("Red",tblTix[#Headers],0)-1,1,1)
我不明白发生了什么。
推荐答案
p>更改
ActiveWorkbook.Names.Add "rngRedLast3Mos", _
"=OFFSET(tblTix,MATCH(MAX(tblTix[Date]),tblTix[Date],1)-3,MATCH(""Red"",tblTix[#Headers],0)-1,3,1)"
到
ActiveWorkbook.Names.Add Name:="rngRedLast3Mos", RefersToR1C1:= _
"=OFFSET(tblTix,MATCH(MAX(tblTix[Date]),tblTix[Date],1)-3,MATCH(""Red"",tblTix[#Headers],0)-1,3,1)"
好
这篇关于在引用ListObjects表的VBA中创建动态命名范围的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!