[已解决]用VBA数组改写公式查找 不要用find也不要用单元格

laoau138

用VBA数组改写公式查找  不要用find也不要用单元格

最佳答案

月排行榜 / 总排行榜

france723

2017-5-27 15:29

因为按钮位置的影响，br没写CurrentRegion（这样不用确定列数）。调整按钮位置后可以改写br = Range("q9").CurrentRegion.

1. Sub aaa()
   
2. Dim y1, ar, br, i, j, n, m, cr, h
   
3. y1 = Range("c65536").End(3).Row
   
4. ar = Range("c10:h" & y1)
   
5. h = Range("q65536").End(3).Row
   
6. br = Range("q9:v" & h)
   
7. ReDim cr(1 To UBound(ar), 1 To 6)
   
8. For i = 1 To UBound(ar)
   
9.     For j = 1 To UBound(ar, 2)
   
10.         For n = 1 To UBound(br)
    
11.             For m = 1 To UBound(br, 2)
    
12.                 If ar(i, j) = br(n, m) Then
    
13.                     cr(i, j) = br(n, 1)
    
14.                 End If
    
15.             Next m
    
16.         Next n
    
17.     Next j
    
18. Next i
    
19. Range("j15").Resize(UBound(cr), 6) = cr
    
20. End Sub
    

复制代码

跳转到最佳答案楼层

france723

因为按钮位置的影响，br没写CurrentRegion（这样不用确定列数）。调整按钮位置后可以改写br = Range("q9").CurrentRegion.

1. Sub aaa()
   
2. Dim y1, ar, br, i, j, n, m, cr, h
   
3. y1 = Range("c65536").End(3).Row
   
4. ar = Range("c10:h" & y1)
   
5. h = Range("q65536").End(3).Row
   
6. br = Range("q9:v" & h)
   
7. ReDim cr(1 To UBound(ar), 1 To 6)
   
8. For i = 1 To UBound(ar)
   
9.     For j = 1 To UBound(ar, 2)
   
10.         For n = 1 To UBound(br)
    
11.             For m = 1 To UBound(br, 2)
    
12.                 If ar(i, j) = br(n, m) Then
    
13.                     cr(i, j) = br(n, 1)
    
14.                 End If
    
15.             Next m
    
16.         Next n
    
17.     Next j
    
18. Next i
    
19. Range("j15").Resize(UBound(cr), 6) = cr
    
20. End Sub
    

复制代码

laoau138

france723 发表于 2017-5-27 15:29
因为按钮位置的影响，br没写CurrentRegion（这样不用确定列数）。调整按钮位置后可以改写br = Range("q9"). ...

小猫高手了

laoau138

france723 发表于 2017-5-27 15:29
因为按钮位置的影响，br没写CurrentRegion（这样不用确定列数）。调整按钮位置后可以改写br = Range("q9"). ...

VBA数组分别计算交集并集差集

http://www.excelpx.com/thread-430616-1-1.html

大灰狼1976

数据量越大的情况下，实际上用工作表函数FIND是效率极高的，而且代码简单。
排序、筛选也是同样道理，微软提供了如此高效的功能，没有必要去回避，除非为了学习。

laoau138

大灰狼1976 发表于 2017-5-27 22:12
数据量越大的情况下，实际上用工作表函数FIND是效率极高的，而且代码简单。
排序、筛选也是同样道理，微软 ...

find读取单元格 超级慢了   一般不可以用

大灰狼1976

laoau138 发表于 2017-5-27 22:30
find读取单元格 超级慢了   一般不可以用

你试下哪个快，第一个过程是生成虚拟数据用，下面两个是FIND和循环的对比。
不要忘记，大数据时，装入数组也是需要时间的。

大灰狼1976

1. Private Sub CommandButton1_Click()
   
2. Dim i&, j&, arr(1 To 10000, 1 To 1000)
   
3. For i = 1 To 10000
   
4.   For j = 1 To 1000
   
5.     arr(i, j) = i * j
   
6.   Next j
   
7. Next i
   
8. [a1].Resize(10000, 1000) = arr
   
9. End Sub
   
10. 
    
11. Private Sub CommandButton2_Click()
    
12. Dim rng As Range
    
13. t = Timer
    
14. Set rng = UsedRange.Find(500000, lookat:=xlWhole)
    
15. MsgBox Timer - t
    
16. End Sub
    
17. 
    
18. Private Sub CommandButton3_Click()
    
19. Dim arr, i&, j&
    
20. t = Timer
    
21. arr = [a1].CurrentRegion
    
22. 
    
23. For i = 1 To UBound(arr)
    
24.   For j = 1 To UBound(arr, 2)
    
25.     If arr(i, j) = 500000 Then GoTo 1
    
26.   Next j
    
27. Next i
    
28. 1
    
29. MsgBox Timer - t
    
30. End Sub

复制代码

laoau138

大灰狼1976 发表于 2017-5-27 22:49

菜鸟一个了，你说的没有认真研究过，一般认为find只能对单元格所以超慢

大灰狼1976

个人经验，对单元格区域操作有几个是非常高效的，第1是工作表排序，第2个是自动筛选，FIND反而优势并没有那么明显，但是代码结构会很简单。
如果数据量很大，而代码中只用到1至2次查找的话，建议使用FIND，因为用数组或其他方法的话，每次装入容器也要浪费很多时间，如果代码中频繁使用查找，那么可以设置全局变量，并装入字典，省去每次装入容器及循环的过程，字典的检索是非常高效的。