range name question 1

[venkman]

I'm trying to write a macro which sets each cell to a vlookup formula. The lookup is on a range in a different workbook which is dynamically chosen by the user at run-time. Is there a way to set a range object and define a name for it, so that it can be used in the formula. My other option seems to be to cumbersomely make a string representing the range, but this seems difficult due to special cases involving special characters in the file path (like ' and <space>). Is there a way to do this?

thanks,
-Venkman

 

[acron]

You could assign the range to an object variable as follows :

Dim rngRange As Range
Set rngRange = Application.InputBox _
(Prompt:=&quot;Select a cell&quot;, Type:=8)

You could then usr rngRange to workk with the selected range.


A.C.

 

[acron]

You could assign the range to an object variable as follows :

Dim rngRange As Range
Set rngRange = Application.InputBox _
(Prompt:=&quot;Select a cell&quot;, Type:=8)

You could then use rngRange to workk with the selected range.


A.C.

 

[venkman]

but if I need to use the range in a vlookup fomula how do I put the range address into the formula. The address property returns the address if you are in the workbook you are using (ie. it gives you the sheet name and cells), but it does not give the file (path and name) of the workbook the range came from. For the moment I've done the inelegant solution of taking the path and name of the workbook, making them into valid strings (by changing ' to '' and adding brackets around the file name), adding the sheet name, and then adding the range. I'd really like a much more elegant one line solution if there is one. Is there a way to represent excel formulas in vb without simply assigning the cell a formula by using a string? for instance does vlookup actually have a vb function or object associated with it that will take a range object as a parameter or property amongst other things?

Oh, and A.C. thank you for responding.

-Venkman

 

[RobBroekhuis]

As best I know, your approach is the only one that works. A bit cumbersome, yes, but it gets the job done.
Rob

 

[venkman]

While I still have not found the one line solution I was looking for, I did find that that using the Address method of the range object with external flag set to TRUE gives you everything needed in the final string except for the path. Again, still not as easy as I'd like it to be, but easier than what I was doing before.
-Venkman

 

[RobBroekhuis]

By golly you're right - you're getting a star on your own thread!
Rob

 

[venkman]

Why thank you! that's the first time I received a star for answering my own question, and I've done that a lot!