ªð¦^¦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)

¦^´_ 10# oobird


    ·PÁÂoobird¤j¤jªº«ü¾É
¥u¬O¤p§Ì·M¬N..©Ò¥HÁÙ¦³´X­Ó°ÝÃD..·Ð½Ð¤j¤j¸Ñ´b~~~
Dim a, r%, i%, arr()
==>°}¦Carrªº«Å§i¬A¸¹¬OªÅ¥Õ..³o¥Nªí¬O«Å§i­þºØ°}¦C?
With Sheets("1")
      a = .Range(.[c10], .[e10].End(4))
==>range¬A¸¹¸Ìªº.[c10], .[e10]³o¼Ë»yªk¬O¦ó·N¸q?¤p§Ì¬Ý¤£À´,¤p§Ì¥u¬Ý¹L«e­±¨S¦³"ÂI"ªº
==>End(4)...³o¼Ëªº»yªk..¤p§Ì¨S¬Ý¹L..©Ò¥H¤£À´~~~~
End With
r = 1
For i = 1 To UBound(a)
       ReDim Preserve arr(1 To 3, 1 To r)
      arr(1, r) = a(i, 1): arr(2, r) = a(i, 2): arr(3, r) = a(i, 3)
        r = r + 6
Next
    Sheets("2").[c4].Resize(r - 6, 2) = Application.Transpose(arr)
    Sheets("2").[k4].Resize(r - 6, 1) = Application.Transpose(Application.Index(arr, 3))

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

¦^´_ 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

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

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

¦^´_ 5# oak0723-1


    Sheet1¬OSheets("2")
§A³o¼Ë«ü¨ìªºa¬ONothing©Ò¥H¥X¿ù
Transpose½Ð¬Ý¤u§@ªí¨ç¼Æ»¡©ú
²³æ¸ÑÄÀ:Âà¸m
¾Ç®üµ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

¥»©«³Ì«á¥Ñ 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

¥»©«³Ì«á¥Ñ 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

        ÀR«ä¦Û¦b : ¥¬¬I¦p¼½ºØ¡A¥HÅw³ß¤ß´þ¼íºØ¤l¡A¤~·|µoªÞ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD