Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return all matching cell values
I don't do much vba but I have a problem that I cannot figure out how
to begin solving: I have a worksheet called "aliases" as follows: column a column b table_name alias_name customer customer1 customer customer2 sales sales2 sales sales2 site site1 What I need to do is create a function or procedure that takes table_name as an input parameter and returns all matching alias values. For example, I would pass "customer" to the process and it would return 'customer1 and customer2" so I can pass those values into another procedure. Regards, Ken |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return all matching cell values
Use code like this:
Function GetAliases(s1 as String) as String Dim rng As Range Dim sAddr As String Dim s1 as String, s as String 's1 = InputBox("Enter the Name to lookup") if s1 = "" then exit sub With Sheets("aliases").Columns(1) Set rng = .Find(What:=s1, _ After:=ActiveCell, _ LookIn:=xlValues, _ LookAt:=xlPart, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlNext, _ MatchCase:=False, _ SearchFormat:=False) If Not rng Is Nothing Then sAddr = rng.Address Do s = s & rng.offset(0,1).Value & vbNewLine Set rng = .FindNext(rng) Loop Until rng.Address = sAddr End If End With if s < "" then s = left(s,len(s)-1) End if GetAliases = s End Function -- Regards, Tom Ogilvy " wrote: I don't do much vba but I have a problem that I cannot figure out how to begin solving: I have a worksheet called "aliases" as follows: column a column b table_name alias_name customer customer1 customer customer2 sales sales2 sales sales2 site site1 What I need to do is create a function or procedure that takes table_name as an input parameter and returns all matching alias values. For example, I would pass "customer" to the process and it would return 'customer1 and customer2" so I can pass those values into another procedure. Regards, Ken |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return all matching cell values
wrote:
. . .I have a worksheet called "aliases" as follows: column a column b table_name alias_name customer customer1 customer customer2 sales sales2 sales sales2 site site1 What I need to do is create a function or procedure that takes table_name as an input parameter and returns all matching alias values. . . . If the functions in the freely downloadable file at http://home.pacbell.net/beban are available to your workbook, you might consider =VLOOKUPS("customer", lookup_array, 2) array entered into a column of cells sufficient to accommodate the output. Alan Beban |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup one value, Return matching correspnding values | Excel Worksheet Functions | |||
Lookup to return multiple matching values | Excel Worksheet Functions | |||
Look up two values in different columns and return matching value | Excel Discussion (Misc queries) | |||
Lookup 3 matching values of which one is a range and return a sum | Excel Worksheet Functions | |||
Return all matching values | Excel Worksheet Functions |