Public Function FindAll(searchFor As String, rngSearchAndFindRange As Range, offsetCol As Integer) As String
Dim Cell As Range
Dim firstFoundCell As Range
Dim returnValue As String
Dim searchRange As Range
If (rngSearchAndFindRange Is Nothing) Then
FindAll = vbNullString
ElseIf (rngSearchAndFindRange.Columns.Count < offsetCol) Then
FindAll = vbNullString
Else
With rngSearchAndFindRange
Set searchRange = Range(.Cells(1, 1), .Cells(.Rows.Count, 1))
End With
With searchRange
Set Cell = .Find(What:=searchFor, _
After:=.Cells(.Rows.Count, .Columns.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False _
)
If Not Cell Is Nothing Then
Set firstFoundCell = Cell
returnValue = Cell.Offset(0, offsetCol - 1).Value
Do While True
Set Cell = .Find(What:=searchFor, _
After:=.Range(Cell.Address), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False _
)
If Not Cell Is Nothing Then
If firstFoundCell.Address = Cell.Address Then Exit Do
returnValue = returnValue & ", " & Cell.Offset(0, offsetCol - 1).Value
Else
Exit Do
End If
Loop
FindAll = returnValue
Else
FindAll = vbNullString
End If
End With
End If
Set Cell = Nothing
Set firstFoundCell = Nothing
Set searchRange = Nothing
End FunctionMembers get more answers than anonymous users.
Being a member gives you detailed monitoring of your requests.
Being a member gives you additional options.