how do i write a function to display a list of information

[Guest]

i tried asking this in an earlier post. i'd like to write a function that
will look for a certain digit in one column of a worksheet, and bring
information from corresponding rows in a different column. i don't want to
count the number of occurrences, but display a list of them all. is this
possible?

 

[Trevor Shuttleworth]

Steph

It might look something like:

Function fFindValues(ByRef FindValue As Range, _
ByRef LookInValues As Range, _
ByVal ColumnOffset As Integer)
' example call: =fFindValues(A1,B:B,1)
' looks for the value in cell A1 in column B
' example call: =fFindValues(A1,B1:B20,1)
' looks for the value in cell A1 in cells B1:B20
' uses ColumOffset to determine where to get the returned value(s)

Application.Volatile
Application.ScreenUpdating = False
Dim cell As Range
Dim FindValues
FindValues = ""
For Each cell In LookInValues
If cell.Value = FindValue Then
If FindValues = "" Then
FindValues = cell.Offset(0, ColumnOffset).Value
Else
FindValues = FindValues & ", " & _
cell.Offset(0, ColumnOffset).Value
End If
End If
Next
fFindValues = FindValues
Application.ScreenUpdating = True
End Function

It's not tested to death but it works in the simple case. Possibly/probably
not the quickest way to do it. Might prompt improvements and alternatives
though.

Note: it's much quicker if you limit the selection to cells rather than the
column. You could, of course, work out how many cells are in use in the
column and restrict it yourself.

Regards

Trevor

 

[Biff]

Hi!

Search column A for 10, return the corresponding values from column B.

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just
ENTER):

=IF(ROWS($1:1)<=COUNTIF(A$2:A$20,10),INDEX(B$2:B$20,SMALL(IF(A$2:A$20=10,ROW(INDIRECT("1:"&ROWS(A$2:A$20)))),ROWS($1:1))),"")

Copy down until you get blanks.

Biff