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

§Q¥ÎFind§ä¤é´Á¡A¥X²{¿ù»~!!

¥»©«³Ì«á¥Ñ stillfish00 ©ó 2018-8-3 10:49 ½s¿è

¦^´_ 19# hugh0620
¨S³o¦^¨Æ¡A19# ÀÉ®×ÁÙ¬O·|¥X²{¿ù»~¡C
ªí¹F¤£²M¡BÃD·N¤£©ú½T¡B¨SªþÀɮ׮榡¡B¨S¦³°Q½×°ÝÃDªººA«×~~~~~~¥H¤W·R²ö¯à§U¡C

TOP

¥»©«³Ì«á¥Ñ hugh0620 ©ó 2018-8-3 15:22 ½s¿è
GÄæ¸ê®Æ®æ¦¡¬O¦Û­q¦Ó«D¤é´Á¡AFind®É·|·í¦¨¦r¦ê¨Ó¤ñ¡A¦ý§A¶Ç¤JFindªºST¬ODate¡A©Ò¥H§ä¤£¨ì ...
stillfish00 µoªí©ó 2018-8-3 10:38


        
À³¸Ó¤£¬O³o­Ó°ÝÃD¡A¦pªG¬O³o­Ó°ÝÃD¡A§ÚÀ³¸Ó­n¥h½Õ¾ãDÄæ¦ìªº®æ¦¡¡A¦ý§Ú¨Ã¨S¦³³o¼Ë¤l³B²z¡A
¶È¬O¦bµ{¦¡½X¤W°µ­×¥¿¦Ó¤w¡C

¦pªG¦³¤ñ¸û¦nªº³B²z¤è¦¡¡A§Ú³£¦³´£¨ÑÀɮסA¥i¥H¶i¦æ­×§ï¤Î´ú¸Õ¡C
  
ÁÙ¬O¤ñ¸û·Q¤F¸Ñ¡A­þ­ÓÀô¸`¥X¤F¿ù»~!!
¾Ç²ß¤~¯à´£¤É¦Û¤v

TOP

¦^´_  hugh0620
¨S³o¦^¨Æ¡A19# ÀÉ®×ÁÙ¬O·|¥X²{¿ù»~¡C
stillfish00 µoªí©ó 2018-8-3 10:39


   
     

´ú¸Õµ²ªG

´ú¸Õµ²ªG.gif
2018-8-3 15:14

     
     ­×¥¿«á¡AÀɮ׬O¥i¥H³Q¥¿±`°õ¦æ!!
     ¤£¤F¸Ñ¡AÀÉ®×ÁÙ¬O·|¥X²{¿ù»~??
     ¬O§_¥i¥HºI¹Ï©Î¬O¤W¶ÇGIFÀɬݤ@¤U!!
¾Ç²ß¤~¯à´£¤É¦Û¤v

TOP

¦^´_ 23# hugh0620
§Úª¾¹D¬°Ô£µ²ªG¤£¤@¼Ë¤F¡C
§A¥i¥H¸Õ¸Õ¥ý«öCtrl+F¡A§A¯à¶]¬O¦]¬°§A¥Ø«eªº¿ï¶µ¬O¦b·j´M'¤½¦¡' ¡A§Úªº¬O¦b·j´M'¤º®e'©Ò¥H°õ¦æ®É¦³Error

Find ªº°Ñ¼Æ¦³«Ü¦h¬OOptional¡A¦ý¬O·í§A¨S«ü©w³o¨Ç°Ñ¼Æ®É¡A¥L·|¥H¤W¦¸¨Ï¥Îªº­È§@¬°¹w³]­È¡C
³o¸Ìªº¤W¦¸¥]§t¨Ï¥ÎªÌ¦³¥Î¨ìCtrl+F·j´M®É¿ï¾Üªº¿ï¶µ¡C

©Ò¥H¡A¥ÎFindªº¸Ü³Ì¦nÁÙ¬O©ú½T«ü©w³o¨Ç°Ñ¼Æ¡C
ªí¹F¤£²M¡BÃD·N¤£©ú½T¡B¨SªþÀɮ׮榡¡B¨S¦³°Q½×°ÝÃDªººA«×~~~~~~¥H¤W·R²ö¯à§U¡C

TOP

¦^´_  hugh0620
§Úª¾¹D¬°Ô£µ²ªG¤£¤@¼Ë¤F¡C
§A¥i¥H¸Õ¸Õ¥ý«öCtrl+F¡A§A¯à¶]¬O¦]¬°§A¥Ø«eªº¿ï¶µ¬O¦b·j´M'¤½ ...
stillfish00 µoªí©ó 2018-8-3 23:09



         Ctrl+Fªº¤è¦¡¡A¦b¹J¨ì°ÝÃDªº®É­Ô¡A¤]¦³¸ÕµÛ¥Î¹L³o¼Ëªº¤è¦¡¡A
        ¤]¦³¼g³]©wLookIn:=xlValues¡A¦ý¤]´ú¸Õ¹L¡A¤£¦æ!!
        
         ¦³¨S¦³¤@­ÓFind¸òFormatªº¬ÛÃö°Ñ¼Æ¤Wªº»¡©ú¸ò½d¨Ò¡A
         ¯à§ó¤è«K¤F¸Ñ¦p¦ó¨Ï¥Î!!
         (¤£¼ô±x³W½d¤Î¨Ï¥Î¤è¦¡¡AÀ³¥Î¤W¡A´N·|«Ü¥d!! ¤]»Ý­n³o¤è­±ªº¨D§U¸ò«ü¾É)
¾Ç²ß¤~¯à´£¤É¦Û¤v

TOP

¥»©«³Ì«á¥Ñ stillfish00 ©ó 2018-8-6 18:20 ½s¿è

¦^´_ 25# hugh0620
Find °Ñ¼Æ»¡©ú : https://msdn.microsoft.com/zh-tw ... e-find-method-excel

¦Û¤v²³æ¸Õ´XºØ¼gªk¡A °£¤F²Ä¤GºØ·|Fail¦p¦P¤§«eÁ¿ªº¡A¨ä¥L³£¯à¦¨¥\§a¡C
  1. Sub TestCases()
  2.     Dim ST As Date
  3.     Dim ED As Date
  4.     ST = ¤u§@ªí7.Range("W3")
  5.     ED = ¤u§@ªí7.Range("X3")
  6.     With ¤u§@ªí7
  7.         Set A = .Range("G3:G10000").Find(What:=ST, After:=.[G3], LookIn:=xlFormulas, LookAt:=xlWhole)
  8.         If Not A Is Nothing Then Debug.Print "#1", A.Row Else Debug.Print "#1", "Find Fail"
  9.         Set A = .Range("G3:G10000").Find(What:=ST, After:=.[G3], LookIn:=xlValues, LookAt:=xlWhole)
  10.         If Not A Is Nothing Then Debug.Print "#2", A.Row Else Debug.Print "#2", "Find Fail"
  11.         Set A = .Range("G3:G10000").Find(What:=Format(ST, "yyyy/mm/dd"), After:=.[G3], LookIn:=xlValues, LookAt:=xlWhole)
  12.         If Not A Is Nothing Then Debug.Print "#3", A.Row Else Debug.Print "#3", "Find Fail"
  13.         Set A = .Range("G3:G10000").Find(What:=.Range("W3").Text, After:=.[G3], LookIn:=xlValues, LookAt:=xlWhole)
  14.         If Not A Is Nothing Then Debug.Print "#4", A.Row Else Debug.Print "#4", "Find Fail"
  15.     End With
  16. End Sub
½Æ»s¥N½X
ªí¹F¤£²M¡BÃD·N¤£©ú½T¡B¨SªþÀɮ׮榡¡B¨S¦³°Q½×°ÝÃDªººA«×~~~~~~¥H¤W·R²ö¯à§U¡C

TOP

¦^´_ 25# hugh0620
³o¸Ì¦³¤@½g¤£ª¾¬O§_¦³À°§U¡H
https://excelmacromastery.com/excel-vba-find/#Using_SearchFormatwith_Find
½Ð¬Ý¡GUsing SearchFormat with Find ½g¤Î¨ä½d¨Ò
¨ä¤¤¦³¤@¥y¡G
Application.FindFormat.Clear
¥i²M°£«e¦¸³]©wªº Find Format

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2018-8-7 07:47 ½s¿è

¦^´_ 25# hugh0620

Range.Find ¤èªk
»¡©ú¤¤ªº l ³Æµù
¦pªG¨S¦³§ä¨ì²Å¦XªºÀx¦s®æ¡A«h¦¹¤èªk·|¶Ç¦^ Nothing¡CFind ¤èªk¤£·|¼vÅT¿ï¨ú½d³ò©Î§@¥ÎÀx¦s®æ¡C
¨C¦¸¨Ï¥Î¦¹¤èªk®É¡A³£·|Àx¦s LookIn¡BLookAt¡BSearchOrder ¤Î MatchByte ªº³]©w¡C¦pªG¤U¦¸©I¥s¦¹¤èªk®É¤£«ü©w³o¨Ç¤Þ¼Æªº­È¡A«K·|¨Ï¥ÎÀx¦s­È¡C¦pªG³]©w³o¨Ç¤Þ¼Æ¡A±N·|Åܧó [´M§ä] ¹ï¸Ü¤è¶ô¤¤ªº³]©w¡A¦Ó­×§ï [´M§ä] ¹ï¸Ü¤è¶ô¤¤ªº³]©w¡A«h·|Åܧó¬Ù²¤³o¨Ç¤Þ¼Æ®É©Ò¨Ï¥ÎªºÀx¦s­È¡C­Y­nÁקKµo¥Í°ÝÃD¡A¨C¦¸¨Ï¥Î¦¹¤èªk®É¡A½Ð©ú½T³]©w³o¨Ç¤Þ¼Æ¡C±z¥i¥H¨Ï¥Î FindNext ¤Î FindPrevious ¤èªk¡A­«½Æ¶i¦æ·j´M¡C

­×§ï19#Àɮתºµ{¦¡
  1. Private Sub CommandButton1_Click()
  2. Dim ST As Date
  3. Dim ED As Date
  4. Dim a As Range
  5. ST = ¤u§@ªí7.Range("W3")
  6. ED = ¤u§@ªí7.Range("X3")
  7. Set a = ¤u§@ªí7.Range("G3:G10000").Find(ST, LookIn:=xlValues)
  8. MsgBox a Is Nothing  &"  §ä¤£¨ì " & ST
  9. 'Find ´M§ä¤é´Á, ¸ê°TªºÃþ«¬,¬°¤½¦¡.
  10. Set a = ¤u§@ªí7.Range("G3:G10000").Find(ST, LookIn:=xlFormulas)
  11. MsgBox a Is Nothing &"  §ä¨ì " & ST
  12. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

°²³]²Ä¤@¦¸¶}±ÒEXCEL«á,¨Ã¥¼°õ¦æ¥ô¦óªº·j´M°Ê§@,
¥u¶i¦æ³oÀɮתºFIND, ¦Ó¦³®É¥i, ¦³®É¤£¥i(½T©w´M§ä­È¬O¦s¦bªº),
«hÀ³¤£¬O·j´M±ø¥ó°Ñ¼Æªº°ÝÃD¤F~~

TOP

­Y¥ÎFIND¤@ª½¦³§xÂZ, ¥i§ï¥ÎMATCH¨ç¼Æ
Dim ST&
ST = Range("W3")
A = Application.Match(ST, [G:G], 0)
If IsError(A) Then MsgBox "§ä¤£¨ì W3 ªº¤é´Á!!!": Exit Sub
MsgBox A

TOP

        ÀR«ä¦Û¦b : ¤â¤ß¦V¤U¬O§U¤H¡A¤â¤ß¦V¤W¬O¨D¤H¡F§U¤H§Ö¼Ö¡A¨D¤Hµh­W¡C
ªð¦^¦Cªí ¤W¤@¥DÃD