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

[µo°Ý] ««ª½Äæ¦ì¶µ¥ØÂà¦Ü¤ô¥­¦CÅã¥Ü

¥»©«³Ì«á¥Ñ ­ã´£³¡ªL ©ó 2015-12-25 18:50 ½s¿è

¦^´_ 10# kasa


1)¦b¥¼¯S§O¼g¥X©w¸qMÅܼÆÄÝ©Ê,¬O¹w³]©w¸q¬°integer¶Ü?¡@
¡Ä¦]MATCH·|²£¥Í¡e¾ã¼Æ¡f¤Î§ä¤£¨ì®É¡e#N/A]¡A©Ò¥H¡AM¤£¥i¨Æ¥ý©w¸q(¹w³]¬°Variant)¡A§_«hµ{¦¡·|¿ù»~¦Ó¤¤Â_¡I¡@
¡@¨ä«á¤~¦³¡@If IsNumeric(M) Then¡@§@§PÂ_¡I¡@

(2)½Ð±z«ü¾É¥H¤U¼g³o3¦æªº¯u¹ê¹ïÀ³§t¸q¡@
   On Error GoTo 999  'µo¥Í¿ù»~®É¡A°õ¦æ¼Ð°O¢¸¢¸¢¸¨º¦æµ{¦¡
¡Ä¦]¤U¤@¦æ¬°¡eÃö³¬¨Æ¥óIJµo¡f¡A­Y¦b¡e¥D³B²z¬q¡fµo¥Í¿ù»~¤¤Â_¡Aµ{¦¡§Y¶]¤£¨ì¡e«ì´_¨Æ¥óIJµo¡f¨º¤@¦æ¡A
¡@·í¡e¨ú®ø¡fµ{¦¡ªº¡e¤¤Â_¡f´£¥Ü«á¡A©Ò¦³ªº¡e¨Æ¥óIJµo¡f§Y¥¢®ÄµLªk¹B§@¡I¡@
¡@¡]µù¡G¦]¥D³B²z¬q¦³¹ï¤u§@ªí°µ Change ªº°Ê§@¡A¬°§K¡e³sÂê¤ÏÀ³¡f¡AÀ³¥ýÃö³¬¡C¡^¡@
¡@Application.EnableEvents = False¡@'Ãö³¬¨Æ¥óIJµo
¡@¡ã¡ã¥D³B²z¬q¡ã¡ã
   999: Application.EnableEvents = True  '«ì´_¨Æ¥óIJµo¡@

(3)xR(1, 2)==>³o­Ó«üªº¬O?
¡ÄFor Each xR In .Cells
¡@­Y±N¸ê®Æ¶K¦b[B3:B6]¡A«h xR ¨Ì¦¸¬° B3.B4.B5.B6¡A
¡@¥H B3 ¬°¨Ò¡AxR(1, 2) µ¥¦P xR.Cells(1, 2)¡A¥ç§Y¬°¨ä¡e¥k¤@®æ¡f C3¡A
¡@xR(1, 2).Resize(1, 99).ClearContents¡@§Y¥HC3¬°©l¡A¦V¥k¨ú¢¸¢¸®æ²M°£¡@

TOP

¤ÓÁÂÁ¤F,²M·¡«Ü¦h¤F

M = Application.Match(xR, [2:2], 0)¡@'§ä¥XItem¦b²Ä¢±¦Cªº¦ì¸m
¦A½Ð°Ý,¦pªG¥u­n³æ¯Â§ä¥XItem¦bG2¨ìM2ªº¦ì¸m,¥Î¥H¤U¼gªk«o¥¢±Ñ,¸Ó¦p¦ó­×¥¿¤~¹ï? ÁÂÁÂ.
M = Application.Match(xR, Range("G2:M2"), 0)

TOP

¤£¦n·N«ä,§Ú§ä¨ì¤èªk¤F,·PÁ«e½åªº¤j¤è«ü¾É,ÁŵM¶}®Ô¦h¤F~~~~~

Set rng2 = [AH1:BQ1]
M = Application.Match(xR, rng2, 0)

TOP

¦^´_ 10# kasa
ÁÂÁ¤j¤jªº´£°Ý ,§Ú¤]¹ï³o¨Ç»y¥y¦üÀ´«DÀ´,²{¦b¦³¸Ñµª¤F.

TOP

·PÁ­ã³ö¤jªº¸Ñ»¡¡A§Ú¤]¬ÝÀ´¤F~

TOP

©êºp¦A´£°Ý¥H¤U,·PÁÂ~
¥H B3 ¬°¨Ò¡AxR(1, 2) µ¥¦P xR.Cells(1, 2)¡A¥ç§Y¬°¨ä¡e¥k¤@®æ¡f C3
=>¥H B3 ¬°¨Ò¡A­Y§ï¦¨xR(1, 1) µ¥¦P xR.Cells(1, 1)¡A¥ç§Y¤´¬°B3,§Ú³o¼Ë²z¸Ñ¬O¹ïªº¶Ü?

TOP

¦^´_ 16# kasa


[B3].cells(1,1)
[B3].offset(0,0)
³£¬O[B3]¥»´L

TOP

¥»©«³Ì«á¥Ñ kasa ©ó 2015-12-28 15:55 ½s¿è

¦A½Ð±Ð­Y»Ý"¦P®É"¥h"¶K¤J"¸ê®Æ©óB/CÄ椧"ÄÝ©Ê"»P"²Ó¥Ø"Äæ¦ì¤¤,
¨ä¥L¶µ¥Ø¤´¬Ò¨Ì"ÄÝ©Ê"¨Ó¤ñ¹ï¥X²Ä¤G¦C¤§Äæ¦ì¨Ã¦b©ÒÄݦP¦C¶ñ¤J"1",¦ý¥u¦³ÄÝ©ÊC»Ý¨Ì"²Ó¥Ø"¤§C1/C2¤ñ¹ï¥X²Ä¤G¦C¤§Äæ¦ì¨Ã¦b©ÒÄݦP¦C¶ñ¤J"1"
¦pªG¤´­n¼g¦bWorksheet_Change¤º,¬O§_¦³¥i¯à¹F¦¨? §Úª¦¤å¼È§ä¤£¨ì¥i¥H¦P®É¦s¦b¨âºØ¤£¦Ptargetªº¤èªk
¦A½Ð«e½úÀ°¦£«ü¾É,·PÁÂ~
Book2.zip (8.08 KB)

TOP

        ÀR«ä¦Û¦b : ¦³´¼¼z¤~¯à¤À¿ëµ½´c¨¸¥¿¡F¦³Á¾µê¤~¯à«Ø¥ß¬üº¡¤H¥Í¡C
ªð¦^¦Cªí ¤W¤@¥DÃD