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 |
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 |
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 |
All times are GMT +1. The time now is 05:17 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com