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

[µo°Ý] ¨ú¥Nªº¼gªk&¤½¦¡µLªk¥¿±`¹Bºâ

[µo°Ý] ¨ú¥Nªº¼gªk&¤½¦¡µLªk¥¿±`¹Bºâ

½Ð±Ð¦U¦ì°ª¤â¡A¦pªþÀÉ~ ¦b»sÁ`ªí_B.rar (43.29 KB)
1.¦³¤@¬q¨ú¥Nªº³¡¤À¤£ª¾¹D¦³¨S¦³¤ñ¸û§Öªº¼gªk¡H
2.¦³¤@¬q¤½¦¡§Ú«Ø¥ßªº®É­Ô¥i¥H¥[Á`¡A¦ý¬O¥Î¥¨¶°½Æ»s¹L¥h¨Ï¥Î®É«oµLªk¨Ï¥Î¡A§Y¨Ï±N¼ÐÃD½Æ»s«á¥H­È¶K¤W¤]¤@¼Ë¡A¦ý¬O­«·s¿é¤J¤SOK¡A¤£ª¾¹D¬O¤°»ò°ÝÃD¡H
3.½Æ»s¹L¥hªº¤½¦¡­n©Ô(½Æ»s)¨ì³Ì«á¤@¦C­n«ç»ò¼g¡H
¤£¦n·N«ä! :L ¤gªk·Ò¿û + ¨ì³Bª¦¤å«÷´ê¥X¨ÓªºªF¦è¦³ÂI­â¶Ã¡A½Ð¦U¦ì°ª¤â¤£§[½ç±Ð¡AÁÂÁ¡I
Adam

¦^´_ 1# adam2010
  1. Sub ex()
  2. With Sheets("data")
  3. .Cells.Clear '²MªÅ¤u§@ªí
  4.    Sheets("Original").UsedRange.Copy .[A1] '½Æ»s¸ê®Æ
  5.    .Columns("C").Replace "*­p", "" '±N§t¦³"­p"ªºÀx¦s®æ²MªÅ
  6.    .Columns("C").SpecialCells(xlCellTypeBlanks).EntireRow.Delete '±N¤W¦C²MªÅªº¦ì¸m¾ã¦C§R°£
  7.    .Columns("A").UnMerge 'AÄæ¨ú®ø¦X¨Ö
  8.    .Rows(1) = .Rows(1).Value '¼ÐÃD¦C­«·sµ¹­È¡A¥i±N­ì¨Ó¼Æ­È¦s¦¨¤å¦r³¡¤À§ï¦¨¼Æ­È
  9.    For i = 6 To 23 '¤ÀÄæ°õ¦æ¨ú¥N°Ê§@¥[§Ö³t«×
  10.       .UsedRange.Columns(i).Replace "-", "" '¥h°£-¸¹
  11.    Next
  12.    .[F:P].Insert '´¡¤JÄæ
  13.    Sheets("¤½¦¡").[F1:P2].Copy .[F1] '½Æ»s¤½¦¡
  14.    r = .Cells(.Rows.Count, 5).End(xlUp).Row '¸ê®Æ¦C¼Æ
  15.    .Range("F2:P2").AutoFill Destination:=.Range("F2:P" & r) '¤½¦¡¦V¤U¶ñº¡
  16. End With
  17. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

·PÁÂHsieh¤j¦p¦¹¨³³t¦a¦^ÂСA¦Ó¥B¦p¦¹Â²µuªºµ{¦¡´N±N§ÚªººÃ°Ý³£¸Ñ¨M¤F¡A
¦Ó¥B»¡©ú«Ü¸ÔºÉ¡AÀ³¸Ó¥i¥H¦Û¤v¬¡¥Î¡A¯u¬O¤Ó·PÁ¤F¡I
Adam

TOP

¥»©«³Ì«á¥Ñ Hsieh ©ó 2013-5-9 14:02 ½s¿è

Dear Hsieh¤j¡A¥Ñ©ó½Æ»s¤½¦¡ÁÙ¦³¤@¬q¬O¦b¤u§@ªí¥k°¼§ÀºÝ(¦ì¸m¤£©T©w)¡A§Ú¬O¥Î
    Selection.End(xlToRight).Select
    ActiveCell.Offset(rowOffset:=0, columnOffset:=1).Activate
    '§ä¨ì³Ì¥kÃä¤@Ä檺®ÇÃ䨺¤@Äæ
        
    Sheets("¤½¦¡").Select
    ActiveWindow.LargeScroll ToRight:=3
    Range("DL1:DS2").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("data").Select
    ActiveSheet.Paste
    '±N¤½¦¡½Æ»s¹L¨Ó
¦ý¬O§Ú­n¦p¦ó§ä¨ì¤½¦¡½d³ò¦V¤U¶ñº¡?
Adam

TOP

¦^´_ 4# adam2010
  1. Sub ex()
  2. With Sheets("data")
  3. .Cells.Clear '²MªÅ¤u§@ªí
  4.    Sheets("Original").UsedRange.Copy .[A1] '½Æ»s¸ê®Æ
  5.    .Columns("C").Replace "*­p", "" '±N§t¦³"­p"ªºÀx¦s®æ²MªÅ
  6.    .Columns("C").SpecialCells(xlCellTypeBlanks).EntireRow.Delete '±N¤W¦C²MªÅªº¦ì¸m¾ã¦C§R°£
  7.    .Columns("A").UnMerge 'AÄæ¨ú®ø¦X¨Ö
  8.    .Rows(1) = .Rows(1).Value '¼ÐÃD¦C­«·sµ¹­È¡A¥i±N­ì¨Ó¼Æ­È¦s¦¨¤å¦r³¡¤À§ï¦¨¼Æ­È
  9.    For i = 6 To 23 '¤ÀÄæ°õ¦æ¨ú¥N°Ê§@¥[§Ö³t«×
  10.       .UsedRange.Columns(i).Replace "-", "" '¥h°£-¸¹
  11.    Next
  12.    .[F:P].Insert '´¡¤JÄæ
  13.    Sheets("¤½¦¡").[F1:P2].Copy .[F1] '½Æ»s¤½¦¡
  14.    r = .Cells(.Rows.Count, 5).End(xlUp).Row '¸ê®Æ¦C¼Æ
  15.    .Range("F2:P2").AutoFill Destination:=.Range("F2:P" & r) '¤½¦¡¦V¤U¶ñº¡
  16.    k = .UsedRange.Columns.Count + 1 '±o¨ì¤w¨Ï¥ÎÄæ¦ì¼Æ
  17.    Sheets("¤½¦¡").[DL1:DS2].Copy .Cells(1, k) '½Æ»s¤½¦¡¨ìªí§À
  18.    .Cells(2, k).Resize(, 8).AutoFill .Range(.Cells(2, k), .Cells(r, k + 7)) '¦V¤U¶ñº¡
  19. End With
  20. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¯u¬O¤Ó¯«¤F¡A¦b³o­Ó½×¾Â¯u±oÄAÂФF§Ú¹ïExcel¥¨¶°ªº¬Ýªk¡A
³Ì­«­nªº¬O«Ü¦h¹³Hsieh¤j¤@¼Ë¼ö¤ßªº¤Hªº¥I¥X¡A¯u¬O¤Ó·PÁ¤F¡I
Adam

TOP

        ÀR«ä¦Û¦b : ¡i®É¶¡µLªk¾B¾×¡j©È®É¶¡®ø³u¡Aªá¤F³\¦h¤ß¦å¡A·QºÉ¦U¦¡¤èªk­n¾B¾×®É¶¡¡Aµ²ªG¬O¡G®ö¶O¤F§ó¦h®É¶¡¡A¥B¤@µL©Ò¦¨¡I
ªð¦^¦Cªí ¤W¤@¥DÃD