excel - 如何使用单元格引用连接多个结果

Question

我有一个宏，可以网络抓取文献数据并返回结果。我当前代码的问题（参考图片）是它在单个列中列出了所有结果（图片参考）。我想使用每个结果的最后一个单元格（SO 然后是下面的空白单元格）作为参考点，将所有相应的作者结果连接到一个单元格中。我不确定使用什么语法可以做到这一点，但任何帮助将不胜感激。

  Sub ReturnFAUResults()

        Dim r As Range

        Application.ScreenUpdating = True

        With Worksheets("Sheet1") ' reference results sheet
        If IsEmpty(.Range("A1")) Then .Range("A1").Value = "dummy header" ' 
        if A1 is empty, put a "dummy" header to make AutoFilter work properly

           .AutoFilterMode = False
        With .Range("B1", .Cells(.Rows.Count, 2).End(xlUp)).Offset(, -1) ' 
        reference referenced sheet column A range from row 1 down to column B 
        last 
             not empty cell
            .SpecialCells(xlCellTypeBlanks).Formula = "=R[-1]C" ' fill 
             referenced range blank cells with the same value as the not 
             empty cell above
            .AutoFilter Field:=1, Criteria1:="=FAU"
             On Error Resume Next
             Set r = .Resize(.Rows.Count - 1, 1).Offset(1, 
             1).SpecialCells(xlCellTypeVisible)
             On Error GoTo 0
             If Not r Is Nothing Then r.Copy Worksheets("Search 
             Results").Range("A7")
            .Parent.AutoFilterMode = False

            .SpecialCells(xlCellTypeFormulas).ClearContents ' clear cell with 
             formulas
             If .Range("A1").Value = "dummy header" Then 
            .Range("A1").ClearContents ' remove any "dummy" header
             End With
             End With

            Application.ScreenUpdating = True

End Sub

返回结果

开始原始结果

每个原始结果的结尾

标签： excelvba