Excel VBA 连接唯一值查找

Question

早上好！我正在尝试创建一个 VBA 函数，并感谢您提供的任何帮助，让我走上正轨。简而言之，对于工作表考试 A 列中的每个值，我需要连接工作表调查结果中 B 列中的所有唯一值，其中工作表考试的 A 列 = 工作表调查结果的 A 列。我正在为从哪里开始而苦苦挣扎，似乎找不到任何好的指导。提前，感谢您的帮助。非常感谢。

从这个开始，让我的方位在连接上......我知道 & ExamID 部分是错误的，但我不确定我需要什么代码来连接该RX721502的下一个实例：

Dim ExamID As Range
Dim strConcat As String
Dim i As Integer
i = 2
Do While Cells(i, 1).Value <> ""
    For Each ExamID In Range("A2:A10000")
        If InStr(ExamID.Value, "RX721502") > 0 Then
        Cells(i, 18).Value = ActiveCell.Offset(0, 10) & ", " & ExamID
        End If
    Next ExamID
    Cells(2, 18) = Trim(Cells(2, 18))
i = i + 1
Loop

G

Answer 1

尝试以下代码，它将城市Concatenate字符串放在工作表源考试第 2 列中。

Sub use_VLookup()

Dim conOG                               As String
Dim SourceExams                         As Worksheet
Dim SourceFindings                      As Worksheet
Dim lastrow, lastrow2                   As Long
Dim rowfound                            As Long
Dim Vlookup_result                      As Variant
Set SourceExams = ActiveWorkbook.Sheets("Source-Exams")
Set SourceFindings = ActiveWorkbook.Sheets("Source-Findings")
lastrow = SourceExams.UsedRange.Rows.count
lastrow2 = SourceFindings.UsedRange.Rows.count
For i = 2 To lastrow
    j = 2
    While j <= lastrow2
        ' search Worksheet Cities workcheet for match on Column A, and return the value in column B
        Vlookup_result = Application.VLookup(SourceExams.Cells(i, 1), SourceFindings.Range(SourceFindings.Cells(j, 1), SourceFindings.Cells(lastrow2, 2)), 2, False)
        If IsError(Vlookup_result) Then
            ' do nothing , you can add erro handling, but I don't think it's necesary
        Else
            conOG = conOG & ", " & Application.WorksheetFunction.VLookup(SourceExams.Cells(i, 1), SourceFindings.Range(SourceFindings.Cells(j, 1), SourceFindings.Cells(lastrow2, 2)), 2, False)
            rowfound = Application.WorksheetFunction.Match(SourceExams.Cells(i, 1), SourceFindings.Range(SourceFindings.Cells(j, 1), SourceFindings.Cells(lastrow2, 1)), 0)
        End If
        j = j + rowfound
        ' if first found go to row 3
        If j <= 2 Then j = 3
    Wend
    SourceExams.Cells(i, 2) = conOG
    conOG = ""
Next i
End Sub