|
本帖最后由 gufengaoyue 于 2014-5-29 16:09 编辑
jacksc 发表于 2014-5-29 12:54
老师:用此方法可以了,但却耗时近300秒,不知还有更简洁快速的办法没?谢谢!
试了几次,大概43秒左右。
- Sub 匹配()
- 'On Error Resume Next
- 'Dim arr, D, a&, x&, t#, MyStr$
- t = Timer '开始记时
- Application.ScreenUpdating = False '关闭屏幕闪烁
- Range("C2:C65536").ClearContents '清除数据
- Set D = CreateObject("scripting.dictionary")
- arr = Sheet2.Range("b2:b" & Sheet2.Range("b65536").End(xlUp).Row)
- For a = 1 To UBound(arr)
- ' If Len(arr(a, 1)) > 0 Then '如果有空白的,打开这里
- l = l + Len(arr(a, 1))
- D(l) = arr(a, 1)
- ' End If
- Next
- arr = Range("b2:c" & Range("b65536").End(xlUp).Row)
- MyStr = Join(D.items(), "|") & "|"
- For a = 1 To UBound(arr)
- If arr(a, 1) <> "" Then
- x = InStr(InStr(1, MyStr, arr(a, 1)), MyStr, "|")
- x = x - (x - Len(Replace(Left(MyStr, x), "|", "")))
- arr(a, 2) = D(x)
- Else
- arr(a, 2) = "没找到数据"
- End If
- Next
- Range("b2:c" & Range("b65536").End(xlUp).Row) = arr
- Application.ScreenUpdating = True '还原屏幕闪烁
- MsgBox "匹配完成;共用时" & Format(Timer - t, "0.00秒。")
- End Sub
复制代码
|
|