Thread: multiple returns from find function

Hi there,

I have a find function I am using with the below macro. It pastes the result into the specified cell but I am wondering...if the search query has more than one result then how do I do this and then paste it into the destination cell?

I have pasted the code below which finds the first value it finds and pastes it into my destination cell.

Code:

Sub sortlinkups()

Dim sourceSheet, destSheet As Worksheet
Dim n As Long
Dim units As String
Dim foundString As Range
Dim destString As String
Dim foundVar As Variant

Set sourceSheet = Sheets("Sheet1")
Set destSheet = Sheets("LB03 CMS Link-Up References")
 
sourceSheet.Activate

For n = 54 To 186

    units = Cells(n, 5).Value
    
    With destSheet
        Set foundString = .Cells.Find(What:=units, After:=ActiveCell, LookIn:=xlValues, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False)
            
    If Not foundString Is Nothing Then
        sourceSheet.Cells(n, 7) = foundString.Offset(0, -1).Value + ", " + sourceSheet.Cells(n, 7).Value
    Else
        Set foundString = .Cells.Find(What:=split_text(units), After:=ActiveCell, LookIn:=xlValues, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False)
            
        If Not foundString Is Nothing Then
        sourceSheet.Cells(n, 7) = foundString.Offset(0, -1).Value + ", " + sourceSheet.Cells(n, 7).Value
        End If
    End If
    End With
Next n

End Sub

I have also pasted the swap string function I am using just for reference:

Code:

Function split_text(data As String) As String
Dim swapped As Variant

   swapped = Split(data, "-")
   split_text = swapped(1) & "-" & swapped(0)

End Function

I hoped there was an easy way to do this but I couldn't find one. If anyone knows then that would really help.

Thanks,
Jag

Hey there, I am looking to add them to the destination cell. So basically concatenate the value with the new value if another is found, example would be:

Code:

sourceSheet.Cells(n, 7) = "the contents of the cell "+ " "+ "the next result returned by the find function"

Jag