ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

[µo°Ý] vba §äÀx¦s®æ¦ì¸m°õ¦æ®Ä¯à½Ð¯q

[µo°Ý] vba §äÀx¦s®æ¦ì¸m°õ¦æ®Ä¯à½Ð¯q

¤p§Ì¥¿¦b³]­p¤@­Óªí
¦ý³o­Óªí¥¼¨Ó¹w´Áªº»Ý¨D·|¹F¨ì¼Æ¸Uµ§

¨ä¤¤¤@­Ó«Ü²³æªº¥\¯à¡A´N¬Ofind¡A«ü©w¤@­Ó¦r¦ê«á¡A¥h§ä¥¦ªº¦ì¸m¡A¥u¬O§Ú±o±N³o¼Æ¸Uµ§¸ê®Æ³£¥h§ä¬ÛÃö¦ì¸m¥X¨Ó

°ÝÃD¨Ó¤F¡A¦³´X¤Q´X¦Êµ§¡A¥Î¤°»ò¤èªk¦ü¥G³£·P¨ü¤£¨ì©úÅã®t²§

¦pªG¼Æ¤d¼Æ¸Uµ§¼vÅT´N¥X²{¤F

½Ð±Ð¡Avba¤¤

¬d§ä¦r¦ê¡G123456789

¤èªk¤@¡Gsheet1.range("A").Find(what:="123456789", lookin:=xlValues).address

¤èªk¤G¡G
redim a()
for i = 1 to lostrow
         ¦r¦ê(i)=sheet1.cells(i,1)
         if  ¦r¦ê(i)="123456789" then
              msgbox i
         end if
next

¥Ø«eÀ|¸Õ³o¤GºØ¤èªk

¤p§Ìª¾¹D¦³¤@ºØ¥sdictionaryª«¥óªº¤èªk¡A°õ¦æ®Ä¯à¶W°ª¡A¦ý¤£ª¾¦p¦óÀ³¥Î¦b¼Æ¸Uµ§¸ê®Æ¤¤¬d§ä¦ì¸m
©Î¬O¯à§_¦³­Ó½d¨Ò²V¦X¹B¥Î°}¦C¤Îdictionaryª«¥ó©O

¤p§Ì¾Ç²L¡A¥Ø«e¥u·|¨Ï¥Î¨ì°}¦C¡A¦ý·Q¨ìº¸«á¼Æ¸Uµ§¸ê®Æ¡A¯uªº«Ü·Q´£°ª®Ä¯à¡AÀµ½Ðª©¤W¦U¦ì¥ý¶i«üÂI¤@¤G¡CÁÂÁ¡C
EXCEL VBA·s¤â¡A½Ð«e½ú¦h¦h«ü±Ð

¦^´_ 8# PKKO


    ·PÁ¡C§Ú±ß¤W¦A¨Ó¸Õ¤@¦¸
EXCEL VBA·s¤â¡A½Ð«e½ú¦h¦h«ü±Ð

TOP

¦^´_ 6# ketrddem


§Ú¶}¤@­Ó·sªºexcelÀÉ®×
½Æ»s§Aªºµ{¦¡ª½±µ¶K¤W¼Ò²Õ
°õ¦æ¤§«á¤@¬í¤§¤º´N¶]§¹Åo
PKKO

TOP

¦^´_ 6# ketrddem

Application.Match ¤]¥i¥H
  1. Option Explicit
  2. Sub Ex()
  3.     Dim T As Date, R As Variant, No As Variant
  4.     No = InputBox("¿é¤J´M§ä¤§¤å¦r,¼Æ¦r")
  5.     If IsNumeric(No) Then No = Val(No)
  6.     T = Time
  7.     With Sheets("¤u§@ªí1").Cells(1, 1).Resize(1000000)
  8.         R = Application.Match(No, .Cells, 0)
  9.          If Not IsError(R) Then
  10.             MsgBox "Find In " & .Range("a" & R).Address(0, 0) & "  " & Application.Text(Time - T, "[S]") & " ¬í "
  11.          Else
  12.             MsgBox "Not Find "
  13.          End If
  14.     End With
  15. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 3# PKKO


    Sub find3()
t = Timer
Rng = Sheets("¤u§@ªí1").Cells(1, 1).Resize(1000000, 1).Value '10000ªº³¡¤À¥i¦Û¦æÅܧó¦C¼Æ
Set D = CreateObject("SCRIPTING.DICTIONARY") '¦r¨åª«¥ó

For i = 1 To UBound(Rng)
     D(CStr(Rng(i, 1))) = i
Next
'³oÃä¬Ý§A­n§ä¬Æ»ò¸ê®Æ,°²³]¬Ox,´N·|¥X²{x¬O²Ä´X¦C¤F

MsgBox D(CStr("987267"))


End Sub

°õ¦æ«á¡A´N¤@ª½¶]¡AµM«áµLªk°±¤î¤F
EXCEL VBA·s¤â¡A½Ð«e½ú¦h¦h«ü±Ð

TOP

¤Ó·PÁ¤G¦ì¤j­ôªº¸Ñ»¡

¤p§Ì°¨¤W¸Õ¸Õ¬Ý
EXCEL VBA·s¤â¡A½Ð«e½ú¦h¦h«ü±Ð

TOP

¥»©«³Ì«á¥Ñ stillfish00 ©ó 2017-3-27 14:28 ½s¿è

¦^´_ 2# ketrddem
§Aªº¤èªk¤G¤£­n¦bFor¸Ì­±¤@­Ó¤@­Ó±qCell¨ú¨ì°}¦C
¦Ó¬O¥ý¤@¦¸¨ú¥X¨Ó...
  1. Sub find2()
  2.     Dim s
  3.     T = Timer
  4.     s = Sheets("¤u§@ªí1").Cells(1, 1).Resize(1000000, 1)
  5.     For i = 1 To 1000000
  6.         If s(i, 1) = "987267" Then
  7.             Exit For
  8.         End If
  9.     Next
  10.     MsgBox "²Ä" & i & "¦æ , " & Timer - T
  11. End Sub
½Æ»s¥N½X
ªí¹F¤£²M¡BÃD·N¤£©ú½T¡B¨SªþÀɮ׮榡¡B¨S¦³°Q½×°ÝÃDªººA«×~~~~~~¥H¤W·R²ö¯à§U¡C

TOP

  1. SUB TEST
  2. RNG=[A1].RESIZE(10000,2).VALUE'10000ªº³¡¤À¥i¦Û¦æÅܧó¦C¼Æ
  3. Set D = CreateObject("SCRIPTING.DICTIONARY") '¦r¨åª«¥ó

  4. FOR I =1 TO UBOUND(RNG)
  5.      D(CSTR(RNG(I,1)))=I
  6. NEXT
  7. '³oÃä¬Ý§A­n§ä¬Æ»ò¸ê®Æ,°²³]¬Ox,´N·|¥X²{x¬O²Ä´X¦C¤F

  8. MSGBOX D(CSTR(X))


  9. END SUB
½Æ»s¥N½X
PKKO

TOP

¸É¥R¡G
¶}·sªº¤u§@ï
´¡¤J¼Ò²Õ
¥H¤U¼Ò²Õ¦Û°Ê¶ñ¤J¤@¨ì¤@¦Ê¸U
Sub test()
For i = 1 To 1000000
Sheets("¤u§@ªí1").Cells(i, 1) = i + 1
Next
End Sub

¬d§ä¤èªk¤@¡Gª½±µ¨Ï¥Î¤º«Ø¨ç±Ð¡A»Ý®É¤@¬í
Sub find1()
t = Timer
If Sheets("¤u§@ªí1").Range("a:a").Find(what:="987267", LookIn:=xlValues).Rows > 0 Then
MsgBox Timer - t
End If
End Sub

¤èªk¤G¡G°j°é¶ñ¤J°}¦C¡A¤Ñ­þ¡A¶·®É¤Q¤@¬í
Sub find2()
ReDim s(1 To 1000000)
t = Timer
For i = 1 To 1000000
s(i) = Sheets("¤u§@ªí1").Cells(i, 1)
If s(i) = "987267" Then
MsgBox Timer - t
End If
Next
End Sub

Àµ¨D²Ä¤TºØ¤èªk¡B²Ä¥|ºØ¤èªk¡B²Ä¤­ºØ¤èªk¡A¤p§Ì´N¬O·Q§ä¨º¤@ºØ¤èªk®Ä¯à³Ì°ª
EXCEL VBA·s¤â¡A½Ð«e½ú¦h¦h«ü±Ð

TOP

        ÀR«ä¦Û¦b : ¦³Ä@©ñ¦b¤ß¸Ì¡A¨S¦³¨­Åé¤O¦æ¡A¥¿¦p¯Ñ¥Ð¤£¼½ºØ¡A¬Ò¬OªÅ¹L¦]½t¡C
ªð¦^¦Cªí ¤W¤@¥DÃD