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

[µo°Ý] ¥Îfind «ü¥O §ä¥X²Å¦XÃC¦âªºÀx¦s®æ ??

[µo°Ý] ¥Îfind «ü¥O §ä¥X²Å¦XÃC¦âªºÀx¦s®æ ??

¥»©«³Ì«á¥Ñ t8899 ©ó 2016-6-21 21:12 ½s¿è

§ä¥X a1:a100 ªºÀx¦s®æ¬° Interior.ColorIndex=35
ªº»yªk??
Interior.ColorIndex=35 ¤£ª¾¦p¦ó®M¥Î??
--------------------------
Find¤èªkªº»yªk
[»yªk]  
<³æ¤¸®æ°Ï°ì>.Find (What¡A[After]¡A[LookIn]¡A[LookAt]¡A[SearchOrder]¡A[SearchDirection]¡A[MatchCase]¡A[MatchByte]¡A[SearchFormat])
[°Ñ¼Æ»¡©ú]
(1)<³æ¤¸®æ°Ï°ì>¡A¥²¶·«ü©w¡Aªð¦^¤@­ÓRange¹ï¶H¡C
(2)°Ñ¼Æ What¡A¥²»Ý«ü©w¡C¥Nªí©Ò­n¬d§äªº¼Æ¾Ú¡A¥i¥H¬°¦r²Å¦ê¡B¾ã¼Æ©ÎªÌ¨ä¥¦¥ô¦ó¼Æ¾ÚÃþ«¬ªº¼Æ¾Ú¡C¹ïÀ³©ó¡u¬d§ä»P´À´«¡v¹ï¸Ü®Ø¤¤¡A¡u¬d§ä¤º®e¡v¤å¥»®Ø¤¤ªº¤º®e¡C
(3)°Ñ¼Æ After¡A¥i¿ï¡C«ü©w¶}©l¬d§äªº¦ì¸m¡A§Y±q¸Ó¦ì¸m©Ò¦bªº³æ¤¸®æ¤§«á¦V«á©Î¤§«e¦V«e¶}©l¬d§ä(¤]´N¬O»¡¡A¶}©l®É¤£¬d§ä¸Ó¦ì¸m©Ò¦bªº³æ¤¸®æ¡Aª½¨ìFind¤èªk¶¦^¨ì¸Ó³æ¤¸®æ®É¡A¤~¹ï¨ä¤º®e¶i¦æ¬d§ä)¡C©Ò«ü©wªº¦ì¸m¥²¶·¬O³æ¤¸®æ°Ï°ì¤¤ªº³æ­Ó³æ¤¸®æ¡A¦pªG¥¼«ü©w¥»°Ñ¼Æ¡A«h±N±q³æ¤¸®æ°Ï°ìªº¥ª¤W¨¤ªº³æ¤¸®æ¤§«á¶}©l¶i¦æ¬d§ä¡C
(4)°Ñ¼Æ LookIn¡A¥i¿ï¡C«ü©w¬d§äªº½d³òÃþ«¬¡A¥i¥H¬°¥H¤U±`¶q¤§¤@¡G
xlValues¡BxlFormulas©ÎªÌxlComments¡AÀq»{­È¬°xlFormulas¡C¹ïÀ³©ó¡u¬d§ä»P´À´«¡v¹ï¸Ü®Ø¤¤¡A¡u¬d§ä½d³ò¡v¤U©Ô®Ø¤¤ªº¿ï¶µ¡C
(5)°Ñ¼Æ LookAt¡A¥i¿ï¡C¥i¥H¬°¥H¤U±`¶q¤§¤@¡G
XlWhole©ÎªÌxlPart¡A¥Î¨Ó«ü©w©Ò¬d§äªº¼Æ¾Ú¬O»P³æ¤¸®æ¤º®e§¹¥þ¤Ç°tÁÙ¬O³¡¤À¤Ç°t¡AÀq»{­È¬°xlPart¡C¹ïÀ³©ó¡u¬d§ä»P´À´«¡v¹ï¸Ü®Ø¤¤¡A¡u³æ¤¸®æ¤Ç°t¡v´_¿ï®Ø¡C
(6)°Ñ¼Æ SearchOrder¡A¥i¿ï¡C¥Î¨Ó½T©w¦p¦ó¦b³æ¤¸®æ°Ï°ì¤¤¶i¦æ¬d§ä¡A¬O¥H¦æªº¤è¦¡(xlByRows)¬d§ä¡AÁÙ¬O¥H¦Cªº¤è¦¡(xlByColumns)¬d§ä¡AÀq»{­È¬°xlByRows¡C¹ïÀ³©ó¡u¬d§ä»P´À´«¡v¹ï¸Ü®Ø¤¤¡A¡u·j¯Á¡v¤U©Ô®Ø¤¤ªº¿ï¶µ¡C
(7)°Ñ¼Æ SearchDirection¡A¥i¿ï¡C¥Î¨Ó½T©w¬d§äªº¤è¦V¡A§Y¬O¦V«e¬d§ä(XlPrevious)ÁÙ¬O¦V«á¬d§ä(xlNext)¡AÀq»{ªº¬O¦V«á¬d§ä¡C
(8)°Ñ¼Æ MatchCase¡A¥i¿ï¡C­Y¸Ó°Ñ¼Æ­È¬°True¡A«h¦b¬d§ä®É°Ï¤À¤j¤p¼g¡CÀq»{­È¬°False¡C¹ïÀ³©ó¡u¬d§ä»P´À´«¡v¹ï¸Ü®Ø¤¤¡A¡u°Ï¤À¤j¤p¼g¡v´_¿ï®Ø¡C
(9)°Ñ¼Æ MatchByter¡A¥i¿ï¡C§Y¬O§_°Ï¤À¥þ¨¤©Î¥b¨¤¡A¦b¿ï¾Ü©Î¦w¸Ë¤FÂù¦r¸`»y¨¥®É¨Ï¥Î¡C­Y¸Ó°Ñ¼Æ¬°True¡A«hÂù¦r¸`¦r²Å¶È»PÂù¦r¸`¦r²Å¬Û¤Ç°t¡F­Y¸Ó°Ñ¼Æ¬°False¡A«hÂù¦r¸`¦r²Å¥i¤Ç°t»P¨ä¬Û¦Pªº³æ¦r¸`¦r²Å¡C
¹ïÀ³©ó¡u¬d§ä»P´À´«¡v¹ï¸Ü®Ø¤¤¡A¡u°Ï¤À¥þ¨¤/¥b¨¤¡v´_¿ï®Ø¡C
(10)°Ñ¼Æ SearchFormat¡A¥i¿ï¡A«ü©w¤@­Ó½T¤ÁÃþ«¬ªº¬d§ä®æ¦¡¡C¹ïÀ³©ó¡u¬d§ä»P´À´«¡v¹ï¸Ü®Ø¤¤¡A¡u®æ¦¡¡v«ö¶s¡C·í³]¸m±a¦³¬ÛÀ³®æ¦¡ªº¬d§ä®É¡A¸Ó°Ñ¼Æ­È¬°True¡C
(11)¦b¨C¦¸¨Ï¥ÎFind¤èªk«á¡A°Ñ¼ÆLookIn¡BLookAt¡BSearchOrder¡BMatchByteªº³]¸m±N«O¦s¡C¦pªG¤U¦¸¨Ï¥Î¥»¤èªk®É¡A¤£§ïÅܩΫü©w³o¨Ç°Ñ¼Æªº­È¡A¨º»ò¸Ó¤èªk±N¨Ï¥Î«O¦sªº­È¡C¦bVBA¤¤³]¸mªº³o¨Ç°Ñ¼Æ±N§ó§ï¡u¬d§ä»P´À´«¡v¹ï¸Ü®Ø¤¤ªº³]¸m¡F¦P²z¡A§ó§ï¡u¬d§ä»P´À´«¡v¹ï¸Ü®Ø¤¤ªº³]¸m¡A¤]±N¦P®É§ó§ï¤w«O¦sªº­È¡C¤]´N¬O»¡¡A¦b½s¼g¦n¤@¬q¥N½X«á¡A­Y¦b¥N
½X¤¤¥¼«ü©w¤W­z°Ñ¼Æ¡A¥i¯à¦bªì´Á¹B¦æ®É¯àº¡¨¬­n¨D¡A¦ý­Y¥Î¤á¦b¡u¬d§ä»P´À´«¡v¹ï¸Ü®Ø¤¤§ó§ï¤F³o¨Ç°Ñ¼Æ¡A¥¦­Ì±N¦P®É¤Ï¬M¨ìµ{§Ç¥N½X¤¤¡A·í¦A¦¸¹B¦æ¥N½X®É¡A¹B¦æµ²ªG¥i¯à·|²£¥Í®t²§©Î¿ù»~¡C­Y­nÁקK³o­Ó°ÝÃD¡A¦b¨C¦¸¨Ï¥Î®É«Øij©ú½Tªº³]¸m³o¨Ç°Ñ¼Æ¡C
Sub test() '§R°£¦Uªí³Ì«á3­Ó¼Æ¾Ú¦æ
   Dim sh As Worksheet, r
   For Each sh In Worksheets
     r = sh.Cells.Find("*", , , , 1, 2).Row¡yªí®æ«DªÅ³Ì¤j¦æ¸¹
   Next
End Sub

¦^´_ 1# t8899

¸Õ¸Õ¬Ý
  1. Option Explicit
  2. Sub test() '§R°£¦Uªí³Ì«á3­Ó¼Æ¾Ú¦æ
  3.    Dim sh As Worksheet, r
  4.    Application.FindFormat.Interior.ColorIndex = 35
  5.    For Each sh In Worksheets
  6.         r = sh.Cells.Find("*", , , , 1, 2, SearchFormat:=True).Row '¡yªí®æ«DªÅ³Ì¤j¦æ¸¹
  7.    Next
  8. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¥»©«³Ì«á¥Ñ t8899 ©ó 2016-6-22 06:33 ½s¿è
¦^´_  t8899

¸Õ¸Õ¬Ý
GBKEE µoªí©ó 2016-6-22 05:57

Snap2.jpg
2016-6-22 06:31


Snap3.jpg
2016-6-22 06:33

TOP

¦^´_ 3# t8899
  1.   r = sh.Cells.Find("", , , , 1, 2, SearchFormat:=True).Row 'ªí®æ³Ì¤j¦æ¸¹
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 4# GBKEE
©Ò¥H·|¥X²{¿ù»~¬O¦]¬° sh ¨Ã¥¼¥[¥H©w¸q¡A
©Ò¥H¥¦ªº°õ¦æ°Ï¶ô·íµM·|¤£³Q±µ¨ü¡C

TOP

http://blog.xuite.net/hcm19522/twblog/205029860

TOP

¦^´_ 4# GBKEE
³o¬O´ú¸ÕªºÀɮסG
A1.png
2016-6-23 15:04

A2.png
2016-6-23 15:05

test.rar (6.84 KB)

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2016-6-24 07:56 ½s¿è

¦^´_ 7# c_c_lai

¥i¤F¸Ñ¤@¤U
  1. Sub Ex() '§R°£¦Uªí³Ì«á3­Ó¼Æ¾Ú¦æ
  2.     Dim sh As Worksheet, r As Double, i As Integer
  3.     Application.FindFormat.Clear
  4.       On Error GoTo Er:
  5.     With Workbooks.Add
  6.         .Sheets(1).Range("a" & Rows.Count) = "test"
  7.         .Sheets(1).Range("a" & Rows.Count) = ""
  8.         For i = 1 To 10
  9.             For Each sh In .Worksheets
  10.             sh.Activate
  11.                 If i > 1 Then
  12.                     On Error GoTo 0
  13.                     With sh
  14.                         .Cells(i, 1) = "aa"
  15.                         .Cells(i, 1) = ""
  16.                     End With
  17.                 End If
  18.                 r = sh.Cells.Find("", , , , 1, 2).Row - 1 ' ªí®æ³Ì¤j¦æ¸¹  (¦Pµ¥©ó SearchFormat:=False)
  19.                 Debug.Print i, sh.Name, r, "¿ù»~­È " & Err
  20.         Next
  21.     Next
  22.     .Close False
  23.     End With
  24. Exit Sub
  25. Er:
  26.     With sh
  27.         .Cells(i) = "aa"
  28.         .Cells(i) = ""
  29.     End With
  30.     Resume
  31. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 8# GBKEE
·P¿E¸U¤À¡I
±q Debug.Print sh.Name, r, "¿ù»~­È " & Err ¤¤
¦h¤Ö¬Ý¥XÁA¤@¨ÇºÝº¸¡A Thanks a lot !

TOP

        ÀR«ä¦Û¦b : ¡i¬O§_µo´§¤F¨}¯à¡H¡j¤H¶¡¹Ø©R¦]¬°µu¼È¡A¤~§óÅã±o¬Ã¶Q¡CÃø±o¨Ó¤@½ë¤H¶¡¡AÀ³°Ý¬O§_¬°¤H¶¡µo´§¤F¦Û¤vªº¨}¯à¡A¦Ó¤£­n¤@¨ý¨Dªø¹Ø¡C
ªð¦^¦Cªí ¤W¤@¥DÃD