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

paddyliu

°Ñ¦Òª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

paddyliu

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

GBKEE

¦^´_ 7# paddyliu

¦p¹Ï: µ{¦¡½X¥i¥Î©ó¨C±i¤u§@ªí

¤U¸ü (34.75 KB)

2013-5-6 12:38

µ{¦¡½X¦p¸m©ó ¨ä¥L¤u§@ªí¼Ò²Õ¤¤
With ActiveSheet.Range("a:a")   §@¥Î¤¤ªº¤u§@ªí

paddyliu

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

paddyliu

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

GBKEE

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

genes

¦^´_ 3# paddyliu


move delete command outside "for loop"?

1. Sub ­åªR§RªÅ()
   
2. 
   
3. For Each a In Range("A:A")
   
4. 
   
5. h = Split(a, "|", 30, vbDatabaseCompare)
   
6. 
   
7.     i = 1
   
8. 
   
9.     For Each b In h
   
10. 
    
11.     a.Offset(columnoffset:=i) = b
    
12. 
    
13.     i = i + 1
    
14.      
    
15.    Next
    
16. 
    
17. Next
    
18. 
    
19. With Range("A1: BB1000").SpecialCells(xlCellTypeBlanks).Delete
    
20. 
    
21. End With
    
22. 
    
23. End Sub

½Æ»s¥N½X

paddyliu

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

Hsieh

¦^´_ 1# paddyliu

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