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

[µo°Ý] §ïµ½®Ä²v°ÝÃD--for-each-next

[µo°Ý] §ïµ½®Ä²v°ÝÃD--for-each-next

¥H¤U¬O¤p§Ì¸g¹Loobird ¤j¤j«ü¾É¼gªºµ{¦¡½X
¥u¬O¹ê°ÈÀ³¥Î¤W¾Þ§@«oµo¥Í¹L©ó½wºCª¬ªp
¤£ª¾­n¦p¦ó­×§ï¤~¥i¥H§ïµ½®Ä²v°ÝÃD
¥t¥~
¦b¤u§@ªí1¤¤¤§¸ê®Æ
³Ì¦h¬O74­Ó
³Ì¤Ö¬O0­Ó
dim i%,j%,k%
i = 4
For Each c In Sheet1.[c10:c83]
Sheet2.Cells(i, 3) = c
i = i + 6
Next
j= 4
For Each c In Sheet1.[d10:d83]
Sheet2.Cells(i, 4) = c
j = j + 6
Next
k = 4
For Each c In Sheet1.[e10:e14]
Sheet2.Cells(i, 11) = c
k = k + 6
Next

990831-1.rar (5.79 KB)

  1. Sub yy()
  2.     Dim a, r%, i%, arr()
  3.     a = Sheet1.[c10].CurrentRegion
  4.     r = 1
  5.     For i = 1 To UBound(a)
  6.         ReDim Preserve arr(1 To 3, 1 To r)
  7.         arr(1, r) = a(i, 1): arr(2, r) = a(i, 2): arr(3, r) = a(i, 3)
  8.         r = r + 6
  9.     Next
  10.     Sheet2.[c4].Resize(r - 6, 2) = Application.Transpose(arr)
  11.     Sheet2.[k4].Resize(r - 6, 1) = Application.Transpose(Application.Index(arr, 3))

  12. End Sub
½Æ»s¥N½X

TOP

¥»©«³Ì«á¥Ñ oak0723-1 ©ó 2010-8-31 18:16 ½s¿è

¦^´_ 2# oobird


½Ð°Ý
oobird ¤j¤j«ü¾É¼gªºµ{¦¡½X
¸g°õ¦æ(¸Ôªþ¥ó)
·|¥d¦b
For i = 1 To UBound(a)
¦ÓµLªk©¹¤UÄ~Äò°õ¦æ
¥t­ì¥»¤u§@ªí1¦b¹ê°È¹B¥Î¤W¸ê®ÆÀ³»P¤u§@ªí1-1Ãþ¦ü
¤£¹L­n°µ»P¤u§@ªí2³sµ²ªº¸ê®Æ
¶È¬°¤u§@ªí1¤§Àx¦s®æc10:e83³o°Ï°ì
¥t¥~
¤p§Ì¯uªº§ä¤£¨ì"Transpose"³o­Ó»¡©ú
©Ò¥H¤£À´³o·N¸q

990831-2.rar (29.52 KB)

TOP

¥»©«³Ì«á¥Ñ Hsieh ©ó 2010-8-31 18:11 ½s¿è

¦^´_ 4# oak0723-1
¥ý¥h¤F¸Ñ°ò¥»»yªk
a = Sheet("1").[c10].CurrentRegion
©Î
a = Sheet2.[c10].CurrentRegion
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 4# Hsieh


    ½Ð°Ý
oobird ¤j¤j«ü¾É¼gªºµ{¦¡½X
¸g°õ¦æ(¸Ôªþ¥ó)
·|¥d¦b
For i = 1 To UBound(a)
¦ÓµLªk©¹¤UÄ~Äò°õ¦æ
¥t­ì¥»¤u§@ªí1¦b¹ê°È¹B¥Î¤W¸ê®ÆÀ³»P¤u§@ªí1-1Ãþ¦ü
¤£¹L­n°µ»P¤u§@ªí2³sµ²ªº¸ê®Æ
¶È¬°¤u§@ªí1¤§Àx¦s®æc10:e83³o°Ï°ì
¥t¥~
¤p§Ì¯uªº§ä¤£¨ì"Transpose"³o­Ó»¡©ú
©Ò¥H¤£À´³o·N¸q

990831-2.rar (29.52 KB)

TOP

¦^´_ 5# oak0723-1


    Sheet1¬OSheets("2")
§A³o¼Ë«ü¨ìªºa¬ONothing©Ò¥H¥X¿ù
Transpose½Ð¬Ý¤u§@ªí¨ç¼Æ»¡©ú
²³æ¸ÑÄÀ:Âà¸m
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¥»©«³Ì«á¥Ñ oak0723-1 ©ó 2010-8-31 19:50 ½s¿è

¦^´_ 6# Hsieh


¹ï¤£°_
¦A½Ð±Ð¤j¤j
¦bvbaÀô¹Ò¤¤­n¨Ï¥Î¤u§@ªí¨ç¼Æ®É
¤£¬OÀ³¸Ó¨Ï¥Î¥H¤U»yªk¶Ü?
"application.worksheetfunction.¤u§@ªí¨ç¼Æ¦WºÙ"
ÁÙ¬OÁÙ¦³¨ä¥LºØ»yªk?
¥t¥~
­Y¤u§@ªí1¤º®e§ï¦¨¦pªþ¥ó0831-3
¦ý¤´µM±ý°µ¤§«e0831-2°Ê§@
À³¸Ó¦p¦ó­×§ïµ{¦¡½Xoobird¤j¤j©Ò¼gªºµ{¦¡½X?

990831-2.rar (12.63 KB)

990831-3.rar (31.3 KB)

TOP

³o¦^Äæ¦ì¼W¦h¤F¡A§A±o­nÅý¤H©ú¥Õ§A·Q±o¨ìªºµ²ªG¬O¤°»ò¼Ë¤l¡C

TOP

¦^´_ 8# oobird


    ©êºpoobird¤j¤j
¤p§Ì¹ê°È¤Wªº¸ê®Æ¦pÀÉ®×990831-3¤@¼Ë
¦ý·Q­n°µªº°Ê§@¬O¦pÀÉ®×990830-2¤@¼Ë
§Ú¨S·Q¨ì2ªÌ·|¸ò¼gµ{¦¡²o³s³o»ò¤j
¤U¦¸­Y¦³°ÝÃD§Ú¦Ò¼{¸Ô²Ó¦A§¹¾ã´£¥X
¯u¬O©êºp~~~~

TOP

¬Ý°_¨Ó§A¤j·§·Q³o¼Ë¡G
  1. Private Sub CommandButton1_Click()
  2. Dim a, r%, i%, arr()
  3. With Sheets("1")
  4. a = .Range(.[c10], .[e10].End(4))
  5. End With
  6. r = 1
  7. For i = 1 To UBound(a)
  8. ReDim Preserve arr(1 To 3, 1 To r)
  9.       arr(1, r) = a(i, 1): arr(2, r) = a(i, 2): arr(3, r) = a(i, 3)
  10.         r = r + 6
  11.     Next
  12.     Sheets("2").[c4].Resize(r - 6, 2) = Application.Transpose(arr)
  13.     Sheets("2").[k4].Resize(r - 6, 1) = Application.Transpose(Application.Index(arr, 3))
  14. End Sub
½Æ»s¥N½X

TOP

        ÀR«ä¦Û¦b : ¥Í®ð¡A´N¬O®³§O¤Hªº¹L¿ù¨ÓÃg»@¦Û¤v¡C
ªð¦^¦Cªí ¤W¤@¥DÃD