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

[µo°Ý] EXCEL VBA ªº°ÝÃD

¦^´_ 9# lpk187

·Q´N±Ð¡A§Aªº²Ä3¦æªº»yªk¡A¬O§_¥i¥Hµy°µ»¡©ú¤@¤U
ÁÂÁÂ

TOP

¦^´_ 11# idnoidno

¬O³o¤@¦C¶Ü¡H
RngRow = ¤u§@ªí1.Columns(Rng.Column).Find("*", , , , , xlPrevious).Row '´M§ä§ïÄ檺³Ì«á¤@­ÓÀx¦s®æ
¨ä¤¤¡A¤u§@ªí1.Columns(Rng.Column)¬O´M§äªº½d³ò©Ò¹ïÀ³ªºÄæ¡A§Ú¥H°ÊºA´`Àô¨Ó§ïÅÜ¥¦´M§äªº½d³ò¡A¥H´`Àôªº²Ä¤@¦¸°j°é¨Ó»¡¡A¥¦·|·j´M"¥Ò"¨º¤@Äæ³Ì«á¦³"­È"ªºÀx¦s®æ¨Ó¶Ç¦^©Ò¹ïÀ³ªº¦C¸¹Row¡A§Ú¥H¸U¥Î¦r¤¸ * ¨Ó¥Nªí¥ô¦ó¦r¦ê¡AxlPrevious¬O¥Ñ¤U©¹¤W§ä,

  ¥H¤U¬OÂ^¨úExcel»¡©úRange.Findªº»¡©úµ¹°Ñ¦Ò¡I
Excel ¶}µo¤H­û°Ñ¦Ò¸ê®Æ
Range.Find ¤èªk
·|´M§ä½d³ò¤¤ªº¯S©w¸ê°T¡C
»yªk

¹Bºâ¦¡.Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)

¹Bºâ¦¡   ¥Nªí Range ª«¥óªºÅܼơC

°Ñ¼Æ

¦WºÙ ¥²­n/¿ï¥Î ¸ê®ÆÃþ«¬ ´y­z
What ¥²­n Variant ­n·j´Mªº¸ê®Æ¡C¥i¥H¬°¦r¦ê©Î¥ô¦ó Microsoft Excel ¸ê®ÆÃþ«¬¡C
After ¿ï¥Î Variant «ü©wÀx¦s®æ¡A·j´M±N·|±q³o­ÓÀx¦s®æ¤§«á¶}©l¡C³o­ÓÀx¦s®æ¹ïÀ³©ó±q¨Ï¥ÎªÌ¤¶­±°õ¦æ·j´M®Éªº§@¥ÎÀx¦s®æ¦ì¸m¡Cª`·N¡AAfter ¥²¶·¬O½d³ò¤¤ªº³æ¤@Àx¦s®æ¡C½Ð°O¦í·j´M¬O±q³o­ÓÀx¦s®æ¤§«á¶}©l¡A¦]¦¹¥²¶·µ¥¨ì¸Ó¤èªk´`Àô¦^¨ì«ü©wªºÀx¦s®æ®É¡A¤~·|·j´M¨ä¤º®e¡C¦pªG¨S¦³«ü©w³o­Ó¤Þ¼Æ¡A«h·j´M·|±q½d³ò¥ª¤W¨¤ªºÀx¦s®æ¤§«á¶}©l¡C
LookIn ¿ï¥Î Variant ¸ê°TªºÃþ«¬¡C
LookAt ¿ï¥Î Variant ¥i¥H¬°¤U¦C¨ä¤¤¤@­Ó XlLookAt ±`¼Æ¡GxlWhole ©Î xlPart¡C
SearchOrder ¿ï¥Î Variant ¥i¥H¬°¤U¦C¨ä¤¤¤@­Ó XlSearchOrder ±`¼Æ¡GxlByRows ©Î xlByColumns¡C
SearchDirection ¿ï¥Î XlSearchDirection ·j´M¤è¦V¡C
MatchCase ¿ï¥Î Variant ¦pªG¬° True¡A«h·j´M®É·|±N¤j¤p¼gµø¬°¬Û²§¡C¹w³]­È¬° False¡C
MatchByte ¿ï¥Î Variant ¥u¦³·í±z¤w¿ï¨ú©Î¦w¸ËÂù¦ì¤¸²Õ»y¨¥¤ä´©®É¡A¤~¯à¨Ï¥Î¡C¦pªG¬° True¡A«hÂù¦ì¤¸²Õ¦r¤¸¥u¯à²Å¦XÂù¦ì¤¸²Õ¦r¤¸¡C¦pªG¬° False¡A«hÂù¦ì¤¸²Õ¦r¤¸¥i²Å¦X¹ïÀ³ªº³æ¦ì¤¸²Õ¦r¤¸¡C
SearchFormat ¿ï¥Î Variant ·j´M®æ¦¡¡C

TOP

¦^´_ 11# idnoidno

§Ú¤]¥u¬O¤@­Óªì¾ÇªÌ¡C¦³ªÅ®É§Ú·|¨Ó³o°Q½×°Ï¬Ý¬Ý¨ä¥L¤j¤jªºµ{¦¡ªº°µªk¡C¶¶«K¥H¨ä¥L¤Hªº°ÝÃD¨Ó·í¦¨§@·~¨Ó§@¡A¬Ý¬Ý¦Û¤v¾Ç¤F¦h¤Ö(¨ä¹ê¦³«Ü¦h¤H°ÝÃD¬Ý³£¬Ý¤£À´)¡A©Ò¥H§Ú¤]·|µ¥¨ä¥L¤j¤jªºµª®×°Õ¡I©Ò¥H¤j®a¤@°_¬ã¨s

TOP

ÁÂÁ¡A½Ð±Ð¡AVBAªºµe­±¤¤¡A±z¦³¨Ï¥Î§Y®ÉºÊ¬Ýµøµ¡¨Ó»²§U±zªºµ{§Çª½¦æ¶Ü

TOP

For Each Rng In ¤u§@ªí1.Range("B1", ¤u§@ªí1.Cells(1, Columns.Count).End(xlToLeft).Address) '²Ä¤@¦C"°ÊºA"Ū¨ú°µ´`Àô
³o¤@¦C¤¤¥Îªk¡A±z¥i§_¦A¤©¥H«ü¾É»¡©ú¤@¤U
¥t¥~°ÊºAŪ¨ú°µ´`Àôªº¤¤¤å·N«ä¦³ÂI¤£²M·¡
¯uªº·PÁ±z¥i¥H¦^´_¡AÁÂÁÂ

TOP

¦^´_ 15# idnoidno


For Each Rng In ¤u§@ªí1.Range("B1", ¤u§@ªí1.Cells(1, Columns.Count).End(xlToLeft).Address) '²Ä¤@¦C"°ÊºA"Ū¨ú°µ´`Àô

©Ò¿×ªº°ÊºA¬O«ü¡A·í§AÀx¦s®æ¦³"¼W´î"®É¤´¥i¥H¨Ì§Aªº¼W´î¨Ó°µÅª¨ú¡A¨Ò¦p¡G¥u¦³¥Ò¤A¤þ3Ä楦´N¥u°µ3¦¸´`Àô¡A­Y¼W¥[2Äæ«h¥i¥H°µ5¦¸ ´`Àô
¤W­± Rng ¬OÀx¦s®æ(Range)ªºÅܼơA
¤u§@ªí1.Range("B1", ¤u§@ªí1.Cells(1, Columns.Count).End(xlToLeft).Address) ¡A«h¬O½d³ò·N«ä¬O«áB1³o¤@Äæ¶}©l¨ì¡AColumns.Count¬O©Ò¦³Ä檺¼Æ¥Ø¡A
End(xlToLeft).Address¬O±q³Ì«áªºÀx¦s®æ´M§ä¦^¨Ó¹J¨ìªº²Ä¤@­Ó¦³­ÈªºÀx¦s®æ¦ì§}

ÁÙ¦³·í¹B¦æµ{§Ç®É­è¶}©l³Ì¦n¥Î"F8"¨Ó¤@¦æ¤@¦æ¬Ý¡A¤j³¡¥÷§Ú¥u¦³¥Î"°Ï°ìÅܼƵøµ¡"ºÊ¬ÝÅܤÆ

TOP

ÁÂÁ±z¡A±zªºÀx¦s®æÅܼƤ£¥Î«Å§i¶Ü?ÁÙ¬O¤@¯ëªì¾Ç´N¥ý¬Ù±¼©O

TOP

¦^´_ 17# idnoidno


    ¦bFor Each Rng ³o­ÓÅܼƤ£¥Î«Å§i
§Ú¤W­±¼gªº¦n¹³¦³ÂIÃøÀ´¡A¥L­ì¨Ó¥i¥H¹³¤U­±³o¼Ë¼g
For Each Rng in Range("B1:D1")    ' ¨ä¤¤Range("B1:D1") ¬O°j°éªº½d³ò
        ...
        ...
Next  
§A¥i¥H¦h°Ñ¦ÒVBAªº»¡©ú

TOP

lpk187¤j¤j¡A«ö±zªº¦^ÂСA±z¤]¬O·|¥h¬d¸ßF1¬d¸ß»¡©ú¥h¬Ý¨Ï¥Îªº¥Îªk¶Ü
ÁÙ¬O¦Ü¤Ö±z¬O¤w¸gª¾¹D¦³³o¼Ëªº¤@­Ó¨ç¼Æ¡A·|¥Î«áµM«á¦A¥h«ö­n¸Ñ¨Mªº¨Ò¤l¦A¥h¬d¬O¤£¬O¥i¥H¦X¾A¸Ñ¨Mªº¶Ü

TOP

¦^´_ 19# idnoidno


    §Ú·|¥h¬Ý»¡©ú¡A¦³®É¬O¦³·|·QªkµM«á¥h¬dÃöÁä¬Ý¬Ý(¤£¤@©w¬d±o¨ì)¬Æ¦Ü¤Wºô§ä¡A¤è¦¡¦³«Ü¦h
¤£¹L»¡©ú­Y¬d±o¨ì¡A¥¦ªº½d¨Ò¤]¬O¾Ç²ßªº¦n¤èªk

TOP

        ÀR«ä¦Û¦b : ¬O«D·í±Ð¨|¡AÆg¬ü§@ĵ±§¡C
ªð¦^¦Cªí ¤W¤@¥DÃD