Multiple VLookUp w. numbered sequencing and line break

[the.white.hole]

Hi Everyone!


I'm having some trouble finishing up a code which aims to search a defined range using vlookup, then returning the results into a single cell, with numbering and line breaks. For example if three rows of data contain the results: pencil, pen, paper, I want the formula to return the results as:

1) Pencil
2) Pen
3) Paper


Currently the code I have returns it as

Pencil
Pen
Paper

without the numbering. If anyone knows how to do this, please help!! Thank you!

Public Function FindSeries(TRange As Range, MatchWith As String)

For Each cell In TRange
If cell.Value = MatchWith Then
x = x & cell.Offset(0, 1).Value & vbNewLine
End If
Next cell

FindSeries = Left(x, (Len(x) - 2))

End Function

SOLUTION BY Roger Govier

Public Function FindSeries(TRange As Range, MatchWith As String)
dim i as long
i= 1
For Each cell In TRange
If cell.Value = MatchWith Then
x = x & i & ") " & cell.Offset(0, 1).Value & vbNewLine
i=i+1
End If
Next cell

FindSeries = Left(x, (Len(x) - 2))

End Function

[Roger Govier]

Hi

Try

Please Login or Register  to view this content.

[the.white.hole]

NICE! It worked!!


Thanks!!