n7822123

¦^´_ 6# Qin


¦r¨åª«¥óªºKey ¦pªG¿é¤Jªº¬O "¦r¦ê"¡A·|¥[§Ö³t«×
³o©Û·Ç¤j¤w¸g¥Î¹L¤£¤Ö¦¸¡A¦hª¦¤å´Nª¾¹D¤F
¬Ýªº¦³ÂIµh­W¡AÀ°§AÁY±Æ¤F

Option Explicit
Sub Ex()
Dim d As Object, E As Range, Ar(), T As Date
T = Time
Debug.Print "³Ìʽ¿ªÊ¼Ê±¼ä : " & T
Dim i As Long
Set d = CreateObject("scripting.dictionary")
With Sheets("Data")
  For Each E In .Range(.[a1], .[a1].End(xlDown))
    d(E.Value & "") = Array(E.Offset(, 1), E.Offset(, 2), E.Offset(, 3))
  Next
End With
   
With Sheets("Search").Range(Sheets("Search").[a2], Sheets("Search").[a2].End(xlDown)).Resize(, 4)
  Ar = .Value
  For i = 1 To UBound(Ar)
     If d.exists(Ar(i, 1)) Then
      Ar(i, 2) = d(Ar(i, 1) & "")(0)
      Ar(i, 3) = d(Ar(i, 1) & "")(1)
      Ar(i, 4) = d(Ar(i, 1) & "")(2)
    Else
      Ar(i, 2) = "No Data"
      Ar(i, 3) = "No Data"
      Ar(i, 4) = "No Data"
    End If
  Next
  .Value = Ar
End With

Debug.Print "³Ìʽ½áÊøʱ¼ä : " & Time, Application.Text(Time - T, "¹²¼Æ[S]Ãë")
End Sub

Dictionary.rar (834.78 KB)

¤U¸ü¦¸¼Æ: 12

2018-11-4 03:52

n7822123

¦^´_ 9# ­ã´£³¡ªL


­ì¨Ó§âÀx¦s®æ¸ê®Æ©ñ¨ì¼Æ²Õ°}¦C«á¡A¦A¸Ë¨ì¦r¨å¸Ì­±
·|¤ñª½±µ®³Àx¦s®æªº­È©ñ¤J¦r¨å¸Ì­±¨Óªº§Ö!!
³o¦³ÂI¹H¤Ïª½Ä±...........

¥H¤U¬O§Ú®³·Ç¤jªºµ{¦¡½X°µ¤@¨Ç­×§ï¨Ó°µ¤ñ¸û
©úÅãtest_1 ¤ñ¸û§Ö

Sub test_1()
Dim T1, d, R%, Arr
T1 = Timer
Set d = CreateObject("scripting.dictionary")
Arr = Range([data!A1], [data!D1].End(4))
For R = 2 To UBound(Arr)
  d(Arr(R, 1) & "") = R
Next R
MsgBox "¦@¯Ó®É" & Round(Timer - T1, 2) & "¬í"
End Sub

Sub test_2()
Dim T1, d, R%
T1 = Timer
Set d = CreateObject("scripting.dictionary")
Sheets("data").Activate
For R = 2 To [A1].End(4).Row
  d(Cells(R, 1) & "") = R
Next R
MsgBox "¦@¯Ó®É" & Round(Timer - T1, 2) & "¬í"
End Sub

n7822123

¦^´_ 10# n7822123

´£¨Ñ¥ÎFind¨Ó¬d§äªº¤èªk¡A¤£¹L¨S¦³¦r¨åª«¥ó¨Óªº§Ö(³æ¯Â¥Î¨Ó´ú¸Õ)

Sub test_3()
Dim R%, T1
T1 = Timer
Application.ScreenUpdating = False
Sheets("Search").Activate  '­Y«ö¶s¦b"Search"­¶­±¥i¬Ù²¤¦¹¦Cµ{¦¡
For R = 2 To [A1].End(4).Row
    On Error Resume Next
      Cells(R, 2).Resize(, 3) = [data!A:A].Find(Cells(R, 1), lookat:=xlWhole).Offset(, 1).Resize(, 3).Value
      If Err = 91 Then Cells(R, 2).Resize(, 3) = "No Data"   '§ä¤£¨ì·|²£¥Í¿ù»~½X:91
    On Error GoTo 0
Next R
MsgBox "¦@¯Ó®É" & Round(Timer - T1, 2) & "¬í"
End Sub

n7822123

¦^´_ 15# Qin


¦r¨å¥i³]©w¬O§_°Ï¤À¤j¤p¼g
¹w³]¼Ò¦¡¤U¡A·|°Ï¤À¤j¤p¼g
§â¦r¨åªºCompareModeÄݩʳ]¬°1¡A§Y¤£¤À¤j¤p¼g
¥H¤U¬OTest½d¨Ò

Sub ex()
Set D = CreateObject("scripting.dictionary")
D.CompareMode = 1      '¦r¨å¤£°Ï¤À¤j¤p¼g
D("abc") = 22
D("ABC") = 55
MsgBox D("abc") & "," & D("ABC")
End Sub

¸Ô²ÓVBA»¡©ú¦p¤U¹Ï

¤U¸ü (18.71 KB)

2018-11-11 11:51