Naming Dynamic Range and turning it into a variable

Question

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.

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

@findwindow Dude I wish you would answer some of these. we need to get you above 2000 so you can edit the questions. — Scott Craner
– Scott Craner, Commented May 18, 2016 at 21:09
You could Debug.Print r.Address it will show you the range address in the immediate window. — Scott Craner
– Scott Craner, Commented May 18, 2016 at 21:13
It is working perfectly. Thank you both very much. @Findwindow I have found out that if you are online my questions get answered much more clearly and quickly than any other time.. — TonyP
– TonyP, Commented May 18, 2016 at 21:16

findwindow · Accepted Answer · 2016-05-18 21:51:49Z

6

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

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

for each c in r debug.print c next

Edit: or if you're cool like Dirk,

Set r = [Win]

edited May 18, 2016 at 21:51

answered May 18, 2016 at 21:16

findwindow

3,2081 gold badge15 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

findwindow Over a year ago

lol This is so not worth 4 upvotes. I mean, this only got the same number of votes and is much much more impressive. Edit: also, whoever did that left me a comment but deleted it before I can catch it...

UnEscapedCharacter Over a year ago

LOL do you want me to downvote it? I won't do it unless you're OK with that...

findwindow Over a year ago

@UnEscapedCharacter I am ok with that.

findwindow Over a year ago

@DirkReichel are you doing that?

UnEscapedCharacter Over a year ago

@findwindow just tried that but I guess I am still too new on SO but thanks for your support, feel free to return the favor anytime, LOL

|

UnEscapedCharacter · Accepted Answer · 2016-05-18 21:25:02Z

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.

Hope that helps!

Glad to help, just don't forget to keep track of all the object variables in the code... give them a name that is easy to understand, like "rngMyEmployeeStatus" and so on...! ;)

Collectives™ on Stack Overflow

Naming Dynamic Range and turning it into a variable

2 Answers 2

6 Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

1 Comment

Linked

Related