Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
define a range that equals the first row of another range
I want to create my own function that is similar to vlookup, but also uses
match function. My understanding is that there is no way to embed worksheet functions within another worksheet function, so I will have to perform the 'match' and 'vlookup' functions separately. In only want to have to import one lookup range for both the vlookup and match functions. Since the vlookup range will include the match range, I am looking for a way to extract the first row. That is match-range equals first-row-of-vlookup-range. How can I define a range that equals the first row of another range? -- Richard |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
define a range that equals the first row of another range
Hi,
You can embed worksheetfunctions in VB and here's an index-match example myvalue = WorksheetFunction.Index(Range("D1:D15"), WorksheetFunction.Match(Range("H1"), Range("B1:B15"))) Perhaps you could explain a bit more clearly what you are trying to do and I'm sure someone will help. Mike "Richard" wrote: I want to create my own function that is similar to vlookup, but also uses match function. My understanding is that there is no way to embed worksheet functions within another worksheet function, so I will have to perform the 'match' and 'vlookup' functions separately. In only want to have to import one lookup range for both the vlookup and match functions. Since the vlookup range will include the match range, I am looking for a way to extract the first row. That is match-range equals first-row-of-vlookup-range. How can I define a range that equals the first row of another range? -- Richard |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
define a range that equals the first row of another range
Mike,
You solved first problem I am having with the embeded function. My Excel book indicated I should use 'application.worksheetfunction' not just 'worksheetfunction'. The longer expression does not embed. The problem I'm still having is that I would like to only have to pass one range through my function, something like this: sub function modified_vlookup(input_value, search_range, column_heading) modified_lookup= vlookup(input_value, search_range, match(column_heading,_ first-column-of-search_range) end function So you see I still need a way of coming up with first-column-of-search_range -- Richard "Mike H" wrote: Hi, You can embed worksheetfunctions in VB and here's an index-match example myvalue = WorksheetFunction.Index(Range("D1:D15"), WorksheetFunction.Match(Range("H1"), Range("B1:B15"))) Perhaps you could explain a bit more clearly what you are trying to do and I'm sure someone will help. Mike "Richard" wrote: I want to create my own function that is similar to vlookup, but also uses match function. My understanding is that there is no way to embed worksheet functions within another worksheet function, so I will have to perform the 'match' and 'vlookup' functions separately. In only want to have to import one lookup range for both the vlookup and match functions. Since the vlookup range will include the match range, I am looking for a way to extract the first row. That is match-range equals first-row-of-vlookup-range. How can I define a range that equals the first row of another range? -- Richard |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
define a range that equals the first row of another range
Richard wrote:
Mike, You solved first problem I am having with the embeded function. My Excel book indicated I should use 'application.worksheetfunction' not just 'worksheetfunction'. The longer expression does not embed. The problem I'm still having is that I would like to only have to pass one range through my function, something like this: sub function modified_vlookup(input_value, search_range, column_heading) modified_lookup= vlookup(input_value, search_range, match(column_heading,_ first-column-of-search_range) end function So you see I still need a way of coming up with first-column-of-search_range Application.Index(search_range, 0, 1) Alan |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
define a range that equals the first row of another range
Alan,
Sorry for not being clearer. Assume I need to do a vlookup and a match statement in the same user defined function. If I imput the vlookup_lookup_range (a 2-D) array, I will need either the first row or first column of vlookup_lookup_range to do the Match function. So how do I get range for the, say, the first row from vlookup_lookup_range? -- Richard "Alan Beban" wrote: Richard wrote: Mike, You solved first problem I am having with the embeded function. My Excel book indicated I should use 'application.worksheetfunction' not just 'worksheetfunction'. The longer expression does not embed. The problem I'm still having is that I would like to only have to pass one range through my function, something like this: sub function modified_vlookup(input_value, search_range, column_heading) modified_lookup= vlookup(input_value, search_range, match(column_heading,_ first-column-of-search_range) end function So you see I still need a way of coming up with first-column-of-search_range Application.Index(search_range, 0, 1) Alan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
AVERAGE a range in a column if another column's range equals a val | Excel Discussion (Misc queries) | |||
Determine if any Cell in Range Equals a String | Excel Programming | |||
if a:a (range) equals january and c:c equals gas then add g:g ($) | Excel Worksheet Functions | |||
Define a range based on another named range | Excel Worksheet Functions |