Return all matching cell values

[kenlkelly]

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

 

[Tom Ogilvy]

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

 

[Alan Beban]

. . .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