在Excel VBA中需要最快的搜索方法 [英] Need Fastest Search Method in Excel VBA

问题描述

考虑一个场景，我有2列（列A和列B）。

Consider a scenario, I have 2 columns (Column "A" & Column "B").

列A有大约13万行/字符串
列B有大约10000行/字符串

Column A has around 130000 rows/Strings Column B has around 10000 rows/Strings

我想从列A搜索列B的每个字符串。

I would like to search each string of Column "B" from Column "A".

可以看到数据量非常高。我已经尝试过Range.Find（）方法。但是花费很多时间来完成。我正在寻找一种可以减少周转时间的方法/方法。

As you can see the volume of data is very high. I have already tried with Range.Find() method. But it's taking lot of time to complete. I am searching for a method/way that will give me result in very less turnaround time.

*我的更多澄清要求 *

* Some more Clarification on my requirement *

（1）列A& B包含字符串值，NOT NUMBERS。并且字符串可以非常大

(1) Column A & B contains string values, NOT NUMBERS. And the string can be very large

（2）对于列B中的每个单元格，列A中可能有许多事件

(2) For each cell in column "B", There can be many occurrence in column "A"

（3）我想以列号

（4）中的A列中的所有列B ）对于列B中存在的字符串。可以在A列中找到任何单元格的子字符串。

(4) For a string present in column "B". It can be found as a Substring of any cell in column "A"

下载文件链接 - wikisend.com/download/431054/StackOverFlow_Sample.xlsx *

Download file link - wikisend.com/download/431054/StackOverFlow_Sample.xlsx *

任何建议？

免费提供您需要任何额外的细节来解决上述问题！

Feel free incase you need any extra details to solve above problem !

推荐答案

尝试这个

在Col A 3 秒 130000行$ c>和 Col B中的10000行。输出在 Col C 中生成。

This took 3 seconds for 130000 rows in Col A and 10000 rows in Col B. The output is generated in Col C.

注意：我已经把最差案例情景，其中Col B中的所有 10000 值都出现在Col A中

NOTE: I have taken the worst case scenario where all 10000 values in Col B are present in Col A

这是我的数据显示。

Sub Sample()
    Debug.Print Now

    Dim col As New Collection
    Dim ws As Worksheet
    Dim i As Long

    Set ws = ThisWorkbook.Sheets("Sheet1")

    Application.ScreenUpdating = False

    With ws
        .Range("C1:C10000").Value = "No"

        For i = 1 To 130000
            On Error Resume Next
            col.Add .Range("A" & i).Value, CStr(.Range("A" & i).Value)
            On Error GoTo 0
        Next i

        On Error Resume Next
        For i = 1 To 10000
            col.Add .Range("B" & i).Value, CStr(.Range("B" & i).Value)
            If Err.Number <> 0 Then .Range("C" & i).Value = "Yes"
            Err.Clear
        Next i
    End With

    Application.ScreenUpdating = True

    Debug.Print Now
End Sub

这是结果

这篇关于在Excel VBA中需要最快的搜索方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！