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

[µo°Ý] ¸ê®Æ­åªR«á§R°£ªÅ¥Õ¦C

[µo°Ý] ¸ê®Æ­åªR«á§R°£ªÅ¥Õ¦C

°Ñ¦ÒªO¤W¤å³¹¸Õ¼g¤@¬q¸ê®Æ­åªR«á§R°£ªÅ¥Õ¦CªºVBA¡A
¸ê®Æ­åªR¥H|²Å¸¹Â_¦æ¡A¦ý°õ¦æ¥X²{¥H¤U°ÝÃD¡G
1.¤@ª½¤£Â_°õ¦æ°j°é
2.§R°£ªÅ¥Õ¦C¬O¤@¾ã¦CªÅ¥Õ¡A¦ý¥Ø«e°õ¦æ¬Ý°_¨Ó¬O§R°£ªÅ¥Õ¦C«á¤U¤èÀx¦s®æ·|¤W²¾¡A
½Ð°ª¤â«üÂI­þ¸Ì¿ù»~¡H·P¿E¤£ºÉ¡I

Sub ­åªR§RªÅ()

For Each a In Range("A:A")

h = Split(a, "|", 30, vbDatabaseCompare)

    i = 1

    For Each b In h

    a.Offset(columnoffset:=i) = b

    i = i + 1
     
   Next
   
   With Range("A1: BB1000").SpecialCells(xlCellTypeBlanks).Delete

End With

Next

End Sub

¦^´_ 1# paddyliu

§R°£¦C¡A·íµM¤U¤èÀx¦s®æ·|¤W²¾¡A¤£ª¾¹D§A·Q­nªº§R°£¬O¬Æ»ò·N«ä?
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 2# Hsieh

§A¦n¡AÁ|¨Ò¦p¤U¡G

°Q¶Å©ú²Ó  ¤@Äýªí
(ªÅ¥Õ¦C)
A125348 | ±i¤p©ú | $1235
V028684 | ³¯¤jªá | $2598
(ªÅ¥Õ¦C)
V248514 | ³¹ªkµØ | $7538
(ªÅ¥Õ¦C)
V425686 | ÂP°Õ°Õ | $5485

§Ú°õ¦æ«áÅܦ¨&¤£Â_°j°é

°Q¶Å©ú²Ó  ¤@Äýªí
                   ±i¤p©ú  $1235
V028684  ³¯¤jªá  $2598
V248514  ³¹ªkµØ  $7538
V425686  ÂP°Õ°Õ  $5485
V425686

¦ý§Ú­nªºµ²ªG¬O¡G
A125348  ±i¤p©ú  $1235
V028684  ³¯¤jªá  $2598
V248514  ³¹ªkµØ  $7538
V425686  ÂP°Õ°Õ  $5485

TOP

¦^´_ 3# paddyliu


move delete command outside "for loop"?
  1. Sub ­åªR§RªÅ()

  2. For Each a In Range("A:A")

  3. h = Split(a, "|", 30, vbDatabaseCompare)

  4.     i = 1

  5.     For Each b In h

  6.     a.Offset(columnoffset:=i) = b

  7.     i = i + 1
  8.      
  9.    Next

  10. Next

  11. With Range("A1: BB1000").SpecialCells(xlCellTypeBlanks).Delete

  12. End With

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

TOP

¦^´_ 3# paddyliu
  1. Option Explicit
  2. Sub Ex()
  3.     Dim E As Range, A As Variant
  4.     With Range("a:a")
  5.         .SpecialCells(xlCellTypeBlanks).Delete             'ªÅ¥Õ¦C§R°£:¶°¤¤¦³¸ê®Æ½d³ò
  6.         For Each E In .SpecialCells(xlCellTypeConstants)   '¦³¸ê®Æ½d³ò
  7.             A = Split(E, "|")                               '¤À³Î¦r¦ê¬°°}¦C
  8.             E.Resize(, UBound(A) + 1).Value = A
  9.         Next
  10.     End With
  11. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 4# genes
·PÁ«ü±Ð¡A³o¼gªkÁöµM¤£·|loop¡A
¦ý§RªÅªº¸ê®Æ·|¥þ³¡¦V¤W¦ì²¾¡A
´N¦P¦p§Ú¤W¤èªº¨Ò¤l¡A
                     ±i¤p©ú  $1235
V028684  ³¯¤jªá  $2598
V248514  ³¹ªkµØ  $7538
V425686  ÂP°Õ°Õ  $5485
V425686
¦pªG¥H§Úªº¼gªk¬O­n§ó§ï¨º¤@¬q©O¡H

TOP

¦^´_ 5# GBKEE
·PÁÂGBKEE¡A»â±Ð¤F¡A¦ý½Ð°Ý³o¥i¥H¸Õ¥Î©ó¨C±i¤u§@ªí¶Ü¡H
¦p§Ú¤µ¤é¶}·sÀÉ®×´¡¤J·s¬¡­¶Ã¯´NµLªk°õ¦æ¤F­C¡H

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2013-5-6 12:43 ½s¿è

¦^´_ 7# paddyliu

¦p¹Ï: µ{¦¡½X¥i¥Î©ó¨C±i¤u§@ªí
EX1.GIF
2013-5-6 12:38


µ{¦¡½X¦p¸m©ó ¨ä¥L¤u§@ªí¼Ò²Õ¤¤
With ActiveSheet.Range("a:a")   §@¥Î¤¤ªº¤u§@ªí
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 8# GBKEE
¦pªG§Ú·Q§â¥¦©ñ¦b±`¥Î©R¥O¦C¡÷¥¨¶°¡÷°µ¤@­Ó§Ö³t¦s¨úªº¹Ï¥Ü¦b¤W¤è¤u¨ã¦C¡A
¥H«á¥u­n«ö¤@¤U´N¥i¥H°õ¦æ¡A½Ð°Ý¤S¸Ó¦p¦ó§@·~©O¡H
ÁÂÁ¡I

TOP

        ÀR«ä¦Û¦b : °µ¸Ó°µªº¨Æ¬O´¼¼z¡A°µ¤£¸Ó°µªº¨Æ¬O·Mè¡C
ªð¦^¦Cªí ¤W¤@¥DÃD