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

Question

>我目前有一个 With 语句，它将一个单元格的值与一系列单元格进行比较，InStr()返回 true，然后将cell.Value标记为"Yes"，如果不是，则"No"。

这是场景。假设我正在检查A2的值，这是111.我正在将111与一系列其他单元格进行比较，并且应该匹配的字段111, 222, 333，因为它包含111。我下面的第一组代码适用于此，但速度很慢。我希望有一种更快的方法，我想我可以用公式做到这一点，但在 vlookup 中使用True而不是False并没有像我想象的那样工作。

这是代码：

With inv_twcg_ws
For Each cell In .Range("A2:A" & .Cells(Rows.Count, "A").End(xlUp).Row)
For Each cellx In report_rng
If InStr(cellx.Value, cell.Value) Then
cell.Offset(0, 6).Value = "Yes"
Exit For
End If
cell.Offset(0, 6).Value = "No"
Next cellx
Next cell
End With

问题是它有点慢。我的应用程序大约需要 5 分钟才能运行。我希望以某种方式将InStr()与Application.Vlookup()结合起来，以尝试加快公式。这可能吗？

这是我当前的Vlookup，但使用True并不能满足我的需求......

With inv_twcg_ws
For Each cell In .Range("G2:G" & .Cells(Rows.Count, "A").End(xlUp).Row)
cell.Value = Application.WorksheetFunction.IfError(Application.VLookup(CStr(cell.Value), report_rng, 1, True), "No")
Next cell
End With

Answer 1

使用变体数组：

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