命名动态范围并将其转换为变量

I am trying to name a range based on a .Find then define that range as my variable so I can enter the variable into a different function. When I run the code I get a type mismatch error.

我试图基于a命名一个范围。然后将该范围定义为我的变量,以便我可以将变量输入到不同的函数中。当我运行代码时,我得到一个类型不匹配错误。

Sub Faked()
Dim r As Range
Cells.Find(What:="EE status", After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Activate
    Range(Selection, Selection.End(xlDown)).Name = "Win"
Set r = ("Win")


End Sub

2 个解决方案

#1

Because Scott says so. Try Set r = range("Win")

因为斯科特这么说。尝试设置r =范围(“赢”)

To test if r picked up the range correctly, one could do

为了测试r是否正确地拾取了范围,可以做到

for each c in r
    debug.print c
next

Edit: or if you're cool like Dirk,

编辑:或者如果你像Dirk一样酷,

Set r = [Win]

#2

Replace the code with this:

用以下代码替换代码:

    Dim r As Range
Cells.Find(What:="EE status", After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Activate
Set r = Range(Selection, Selection.End(xlDown))
r.Name = "Win"

Explanation:

You cannot use the SET command with the name property of the range object. The object needs to be populated with the range selection first, then you can just use the property of the object to assign a value to it, in this case the Name property of it.

您不能将SET命令与范围对象的name属性一起使用。首先需要使用范围选择填充对象,然后您可以使用对象的属性为其赋值,在本例中为其Name属性。

Hope that helps!

希望有所帮助!

#1