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

[µo°Ý] ½Ð±Ð·L½Õ«ö¶s¦bVBA¸Ìªº¼gªk

[µo°Ý] ½Ð±Ð·L½Õ«ö¶s¦bVBA¸Ìªº¼gªk

¤£ª¾¹D¬O§_¥i¥H¼g¤@­ÓVBA¬O¦b²Ä¤T¤À­¶ªº·L½Õ«ö¶s
¨C©¹¤W·L½Õ°õ¦æ¤@¦¸¤§«á¡A·|¦Û°Ê§â²Ä¤T¤À­¶ªºH4~H10¶K¤W­È¹ïÀ³¨ì²Ä¤G¤À­¶ªºBÄæ¨ìHÄæ¤U¤è
ÀH¸ê®ÆÅܰʸòµÛ¶K¤W­Èªº°j°é¡A¥B¹ïÀ³¨ì¥¿½Tªº½s¸¹«á¤èÀx¦s®æ

·Ð½Ð«e½ú­Ì±Ð¾Ç¡A¤§«á¾Ç°_¨Ó§Æ±æ¥i¥H¦P²zÀ³¥Î¦b³\¦h¸ê®Æ¤ÀªR¤W

ÀÉ®×.rar (29.71 KB)

¢°¡D¤½¦¡ªk¡G
¢Ð¢±¡G
=SUM(N(OFFSET(Sheet1!$A$1,MATCH($A2,Sheet1!$A:$A,)-1,MATCH(B$1&"/close",Sheet1!$1:$1,)-{6,1}))*{-1,1})
¡@
¢±¡DVBA¡G°j°é¤@¦¸¶]§¹
Sub T1014_1()
Dim R&, xR As Range, X&, j&
With Sheets("Sheet2")
¡@¡@¡@Set xR = .[B1]
¡@¡@¡@R = .Cells(Rows.Count, 1).End(xlUp).Row - 1
¡@¡@¡@If R <= 0 Then Exit Sub
End With
¡@
With Sheets("Sheet3")
¡@¡@¡@X = .Cells(Rows.Count, 1).End(xlUp).Row - 3
¡@¡@¡@If X <= 0 Then Exit Sub
¡@¡@¡@For j = 1 To R
¡@¡@¡@¡@¡@.[A2] = j
¡@¡@¡@¡@¡@Set xR = xR(2)
¡@¡@¡@¡@¡@xR.Resize(1, X) = Application.Transpose(.[H4].Resize(X))
¡@¡@¡@Next
End With
End Sub

TOP

¸Õ¶]¤F¤@¤U¡AVBA»PÀx¦s®æ¤½¦¡¡A³£¯à¹F¨ì§Ú­nªº»Ý¨D
¦Ó¥B¦Û¤vµy°µ­×§ï³£¥i¥HÀ³¥Î¦b¨ä¥L¸ê®Æ¤ÀªR¤W­±
­ã´£³¡ªL«e½ú¡A·PÁ§A¦A¦¸¦^µª§Úªº°ÝÃD
ÁöµMµ{¦¡½X¥u¯à¤j·§¬Ý±oÀ´¦p¦ó¹B¦æªº¡A¥ý©êµÛ·P®¦ªº¤ß¦¬¤U¤F
µy°µ¬ã¨s¤F¤@·|µ{¦¡½X¡A²Ä¤G¬qµ{¦¡½X¦³¨Ç¤£À´ªº¦a¤è¡A·Q¦h°µ¤F¸Ñ
¤£ª¾¬O§_¥i¥H¸ÑÄÀ¤@¤U³o¬qµ{¦¡ªºÅÞ¿è¡A¦nÂç²M»P½T©w§Ú¦Û¤vªº²z¸Ñ¦³¨S¦³¿ù»~
With Sheets("Sheet3")
¡@¡@¡@X = .Cells(Rows.Count, 1).End(xlUp).Row - 3
¡@¡@¡@If X <= 0 Then Exit Sub
¡@¡@¡@For j = 1 To R
¡@¡@¡@¡@¡@.[A2] = j
¡@¡@¡@¡@¡@Set xR = xR(2)
¡@¡@¡@¡@¡@xR.Resize(1, X) = Application.Transpose(.[H4].Resize(X))

TOP

¥»©«³Ì«á¥Ñ ­ã´£³¡ªL ©ó 2015-10-15 09:43 ½s¿è

¦^´_ 3# ®®­·


X = .Cells(Rows.Count, 1).End(xlUp).Row - 3  
¡Ä¡e¤é´Á¡fµ§¼Æ¡A­ìÀ³´î¢±¡A¦ý¶·¦Ü¤Ö¢±µ§¥H¤W¤~¯àºâ¤W¤U®t­È¡A¬G¦h´î¢°¡@
¡@
If X <= 0 Then Exit Sub
¡Ä¤é´Á¤Ö©ó¢±­Ó¡D¸õ¥X¡@

For j = 1 To R
¡Ä¥H Sheet2 ½s¸¹­Ó¼Æ¶]°j°é¡@

.[A2] = j
¡Ä¦b¢Ï¢±¶ñ¤J°j°é­È¡A¬Û·í¤_¨Ï¥Î·L½Õ«ö¶s³s«ö¡A¨Ï¤½¦¡¦Û°ÊÅÜ´«­pºâ­È¡@

Set xR = xR(2)
¡Ä­nÂà¶K®t­Èªº¦ì¸m¡A°_©l®æ¬° Sheet2 ªº¢Ð¢°¡A¶]°j°é®É¦Û°Ê¡e©¹¤U¡f²¾¤@®æ¡A§Y¢Ð¢±.¢Ð¢².......¡@

xR.Resize(1, X) = Application.Transpose(.[H4].Resize(X))
¡Ä¥H xR ¬°°ò·Ç¡A¦V¥k§ì X Äæ¼Æªº½d³ò¡A¶ñ¤J¢ÖÄæ®t­È¡@
¡Ä¦]¥Ñ¡eª½Âà¾î¡f¡A¥²¶·¨Ï¥Î Transpose ¡eÂà¸m¡f¡@

TOP

·PÁ«e½ú±Ð¾É¡A¬Ý¤FÅÞ¿è¸ÑÄÀÀ´¤F³\¦h¡A¤]¾Ç¤F³\¦h
¥Îªk¥i¥H¦p¦¹²M´·»PÆF¬¡¡A§Æ±æ¥H«á§Ú¤]¯à°µ¨ì¡AÁ«ü±Ð

TOP

        ÀR«ä¦Û¦b : ¨Ã«D¦³¿ú¾{¬O§Ö¼Ö¡A°Ý¤ßµL·\¤ß³Ì¦w¡C
ªð¦^¦Cªí ¤W¤@¥DÃD