是否可以将InStr()与Application.Vlookup()结合使用？

1> Scott Craner..：

---

使用变体数组:

Dim report_ws As Worksheet
Set report_ws = report_wb.Worksheets(1)

Dim report_rng As Variant
report_rng = report_ws.Range("B2:B" & last_row)

Dim inv_twcg_ws As Worksheet
Set inv_twcg_ws = Worksheets("Sheet1") ' change to your sheet




With inv_twcg_ws
    Dim lkup_rng As Variant
    lkup_rng = .Range("A2:A" & .Cells(Rows.Count, "A").End(xlUp).Row).Value

    Dim otpt As Variant
    ReDim otpt(1 To UBound(lkup_rng, 1), 1 To 1) As Variant

    Dim i As Long
    For i = LBound(lkup_rng, 1) To UBound(lkup_rng, 1)
        otpt(i,1) = "No"
        Dim j As Long
        For j = LBound(report_rng, 1) To UBound(report_rng, 1)
            If InStr(report_rng(j, 1), lkup_rng(i, 1)) Then
                otpt(i,1) = "Yes"
                Exit For
            End If
        Next j
    Next i

    .Range("G2").Resize(UBound(otpt, 1)).Value = otpt
End With

---

@QHarr我实际上已经用OP的最后一个问题开始了这个问题,但是当我的评论解决了这个具体问题时就停止了.

---

@ Mike-SMT工作表读取和写入几乎是您在Excel + VBA中可以做的最慢的事情 - 学会喜欢内存数组,他们会爱你的!