VBA Range(cell1,cell2) as Worksheet Function

**Tomski** · 03-18-2009, 07:40 AM

Hi Guys,

I was wondering if there was a work sheet function that returns a range rather that one cell. I'm looking to use a vlookup, but I need to find the range first. This would be very similar to the using Range(A:2, D:10) when coding VBA. I expect my sheet formula to look roughly like this:

vlookup(A2,Range(Index(2,Match()),offset(Index(2,Match()),20,1)),2,false)

I've call the function I'm looking for Range. I suppose another thing is can Vlookup take a function that outputs a range as an arguement as I could always write one. Just I thought Excel would have come with a useful function such as this.

Any ideas?

Thanks again

,

T

**Kenneth Hobson** · 03-18-2009, 08:14 AM

Look for help using: =Indirect

**DonkeyOte** · 03-18-2009, 08:21 AM

Please post code in VBA tags (even pseudo-code) - to do this Edit your last post, highlight pseduo-code and press the # icon.

Re: your question... it seems you want to find a value in row 2 and create a range 20 rows x 2 columns, is that correct ?

So perhaps something along the lines of:

Please Login or Register  to view this content.

Note use of Application.Function over WorksheetFunction.Function ... the former makes life easier handling errors as you can see with vMatch... if no Match of "somevalue" is found on Row 2 vMatch will hold an Error value and thus VLookup won't proceed... if VLookup fails vAns will hold Error value else it will hold resulting value.

In truth I'm find it hard to interpret/visualize what it is you're trying to do... the above is a stab in the dark.

**Tomski** · 03-18-2009, 08:51 AM

Thanks for both responses.

In regards to the Indirect function, this still only returns one cell, while it seems useful I still don't understand how it solves what I'm trying to do.

I'll try and be a little bit clearer. I originally put the post in the functions forum, as I'm not interested in VBA (shouldn't have really included it in the title), hence I didn't use the code tags because it was really code, just work sheet functions.

I'm just trying to find out if there is a function that returns a range, say (A2:G10) that could then be used as a range argument to a vlookup. This function would behave similar to the Range method used when coding VBA. So one could then use, two Index functions, the function I'm looking for (if it exists) would use these as inputs and return a range, this in turn could be used as an argument for a vlookup, our any other function that takes a range as an input.

Thanks again

**Kenneth Hobson** · 03-18-2009, 09:20 AM

I don't think that you understand. You can use & or =Concatenate to build your reference string.

e.g.
=COUNT(A1:A10)
=COUNT(INDIRECT("A1:A10"))
=COUNT(INDIRECT(CONCATENATE("A1",":","A10")))

**DonkeyOte** · 03-18-2009, 09:35 AM

Not VBA... interesting thread title...

I would argue you should use Range:INDEX or INDEX:INDEX over INDIRECT, eg along the lines of:

=VLOOKUP(A1,A2:INDEX(B2:B10,MATCH("value",A2:A10,0)),2,0)

So this would conduct a vlookup of A1 in range A2:Bx where x is determined by position of "value" in range A2:A10.

This is I should add a non-sensical example but is meant to illustrate the concept of a Range:INDEX or INDEX:INDEX approach to create a range for use elsewhere.

**Tomski** · 03-18-2009, 10:31 AM

Thanks again both of you for the replies.

Have manged to solve the problem now, and also learn about some other parts of excel I didn't know about. Great!

I think the thing I was missing here was that cell1:cell2 in on the worksheet is the same as the range method used in VBA, Range(cell1,cell2), both can be used for arguements in the same way.

Thanks.

On another note good to see a someone from Suffolk, I'm from Great Wratting near Haverhill myself.

T