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

[µo°Ý] À°¦£µù¸Ñ¤@¤U¡AÁÂÁÂ!

[µo°Ý] À°¦£µù¸Ñ¤@¤U¡AÁÂÁÂ!

¥»©«³Ì«á¥Ñ olisun ©ó 2010-8-20 15:57 ½s¿è

À°¦£µù¸Ñ¤@¤U¡AÁÂÁÂ!
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address <> "$E$2" Then Exit Sub
Dim i&, Myr&, col%, Arr, Arr1(1 To 6, 1 To 12), Arrth
Application.ScreenUpdating = False
Myr = Sheets("¦¨¥æ«È¤á").[a65536].End(xlUp).Row
Arr = Sheets("¦¨¥æ«È¤á").Range("a3:r" & Myr)
Arrth = Sheets("°h¤á«È¤á").[a1].CurrentRegion-------³o¤@¥y¬O¬Æ»ò·N«ä
For col = 1 To UBound(Arr, 2) Step 6-------³o¤@¥y¬O¬Æ»ò·N«ä
    For i = 1 To UBound(Arr)-------³o¤@¥y¬O¬Æ»ò·N«ä
        If Arr(i, col) = "" Then Exit For
        If Arr(i, col) < Target.Value Then

¨S¼gµù¸Ñ¤°»ò¡A¬Ý¨Ó¥u¦³°ª¤â¤¤°ª¤â¤~ª¾¹D
¤p§Ì¬Oexcelµ{¦¡ªì¾ÇªÌ¡A½Ð¦h¦h«ü±Ð

TOP

À°¦£µù¸Ñ¤@¤U¡AÁÂÁÂ!
Application.ScreenUpdating = False '¤£àDµo¨ä¥¦¨Æ¥óªº¥N½X
olisun µoªí©ó 2010-8-20 14:06


Application.ScreenUpdating = False '¤£àDµo¨ä¥¦¨Æ¥óªº¥N½X
³o¥yÀ³¸Ó¬O..Ãö±¼¿Ã¹õ§ó·s(µ{¦¡¹B§@®É,µe­±¤£·|¸òµÛ§ó´«,¥i¥H¼W§Ö¤@¨Ç³t«×)

¦pªG¬O­nÃö±¼Ä²µo¨Æ¥ó..
¥i§ï¥Î Application.EnableEvents = False

TOP

À°¦£µù¸Ñ¤@¤U¡AÁÂÁÂ!
Arr = Sheets("¦¨¥æ«È¤á").Range("a3:r" Myr)
Arrth = Sheets("°h¤á«È¤á").[a1].CurrentRegion-------³o¤@¥y¬O¬Æ»ò·N«ä
For col = 1 To UBound(Arr, 2) Step 6-------³o¤@¥y¬O¬Æ»ò·N«ä
    For i = 1 To UBound(Arr)-------³o¤@¥y¬O¬Æ»ò·N«ä
olisun µoªí©ó 2010-8-20 14:06


¸ÕµÛ¸ÑÄÀ¬Ý¬Ý,¦p¦³¿ù»~,ÁٽнѦì¤j¤j­×¥¿,·PÁÂ!

Arr = Sheets("¦¨¥æ«È¤á").Range("a3:r" Myr)
'³o¥yÀ³¸Ó­×¥¿¬° Arr = Sheets("¦¨¥æ«È¤á").Range("a3:r" & Myr)

Arrth = Sheets("°h¤á«È¤á").[a1].CurrentRegion
'±N Sheets("°h¤á«È¤á") ¦³¸ê®Æªº°Ï°ì©ñ¤J Arrth ¸Ì­±

For col = 1 To UBound(Arr, 2) Step 6
'¨Ì¾Ú (Arr,2) ªº¤º®e,©ñ¤J col ¸Ì­±,¨C¦¸¶¡¹j¬° 6
'²Ä¤@µ§ col ¬° Sheets("¦¨¥æ«È¤á") ¦b B3 Äæ¦ìªº­È,²Ä¤Gµ§¬° B9 Äæ¦ìªº­È..¥H¦¹Ãþ±À

For i = 1 To UBound(Arr)
'¨Ì·Ó°}¦C¸ê®Æµ§¼Æ¶i¦æ§@·~
'°²³] Arr ¦@¦³ 10 µ§,«h¶i¦æ 10 ¦¸°j°é
'­Y¨Ì±zªºµ{¦¡,°j°é¦¸¼Æ¨M©w©ó Sheets("¦¨¥æ«È¤á").[a65536].End(xlUp).Row -2

TOP

¥»©«³Ì«á¥Ñ Hsieh ©ó 2012-3-14 08:23 ½s¿è

CurrentRegion
¶Ç¦^ Range ª«¥ó¡A¸Óª«¥ó¥Nªí¥Ø«eªº°Ï°ì¡C¥Ø«e°Ï°ì¬O«ü¥H¥ô·NªÅ¥Õ¦C¤ÎªÅ¥ÕÄ檺²Õ¦X¬°Ãä¬Éªº½d³ò
Arrth = Sheets("°h¤á«È¤á").[a1].CurrentRegion
´N¬O§â¸òa1Àx¦s®æ¬Û³sªº½d³ò¦s¦¨°}¦CArrth
For col = 1 To UBound(Arr, 2) Step 6
¦]¬°Arr¬OA3:R?³o½d³òªº°}¦C
©Ò¥H¬OArr(1 TO ?-3,1 TO 18)³o¬O¦]¬°¥Ñ½d³òª½±µ¼g¤J°}¦C©Ò¥H¤U¼Ð±q1¶}©l
UBound(Arr, 2)´N¬OArrªº²Ä2ºû¤W¼Ð¬É´Nµ¥©ó18
Step 6´N¬O¨C¦¸¸õ6Äæ
For i = 1 To UBound(Arr)
UBound(Arr)´N¬O²Ä¤@ºû¤W¼Ð¬É
´N¬O¨Ì·Ó¨C¦C¸ê®Æ¤@¤@¶]§¹
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

ÁÂÁ¤G¦ì¤j­ôªº¸Ñ»¡

TOP

        ÀR«ä¦Û¦b : ¡i¬°µ½Ävª§¡j¤H¥Í­n¬°µ½Ävª§¡A¤À¬í¥²ª§¡C
ªð¦^¦Cªí ¤W¤@¥DÃD