问VBA代码以查找其旁边的值复制值并粘贴在不同的单元格中

EN

Public Sub FindingValues()
    Dim val As String, result As String, firstAddress As String
    Dim c As Range

    val = "Title_product"
    Set c = Sheets("MySheetName").Range("A:A").Find(val, LookIn:=xlValues, MatchCase:=False)

    If Not c Is Nothing Then
        firstAddress = c.Address

        Do
            If Len(result) > 0 Then
                result = result & " and " & c.Offset(, 2).Text
            Else
                result = c.Offset(, 2).Text
            End If

            Set c = Cells.FindNext(c)
        Loop While Not c Is Nothing And c.Address <> firstAddress
    End If

    Sheets("MySheetName").Range("A10").Value = result
End Sub

Sub FindingValues()
Dim valStr As String, found() As String, outputStr As String
Dim outputCell As Range
Dim ws As Worksheet
Dim x As Long, foundCt As Long, lastRow As Long

'define the sheet to be worked on
Set ws = ActiveWorkbook.ActiveSheet

'define the output cell
Set outputCell = ws.Cells(10, 1) 'A10

'define the text to search for
valStr = "Title_product"

'find last row to use
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

'change the array size to match the number of possible results
ReDim found(lastRow) As String

'loop through all cells in column A
For x = 1 To lastRow

    'check cells in column A for valStr
    If InStr(LCase(ws.Cells(x, 1).Text), LCase(valStr)) Then

        'if found, store found values in array
        foundCt = foundCt + 1
        found(foundCt) = ws.Cells(x, 3).Text

    End If

Next x

'format the output based on the number of found items
'(Book 1 | Book 1 and Book 2 | Book 1, Book 2, and Book 3)

'if no results were found
If found(1) = "" Then
    outputStr = "..."

'if one result was found
ElseIf found(1) <> "" And found(2) = "" Then
    outputStr = found(1)

'if two results were found
ElseIf found(1) <> "" And found(2) <> "" And found(3) = "" Then
    outputStr = found(1) & " and " & found(2)

'if three or more results were found
Else
    outputStr = found(1)
    For x = 2 To foundCt - 1
        outputStr = outputStr & ", " & found(x)
    Next x
    outputStr = outputStr & ", and " & found(foundCt)
End If

'print the output to the output cell
outputCell.Formula = outputStr

End Sub