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

½Ð°Ý¦p¦ó±N¸ê®Æ"°Å¤U"µM«á¦V¤¤¶¡ªºªÅ®æ¦V¤W¶K»ô©O?

½Ð°Ý¦p¦ó±N¸ê®Æ"°Å¤U"µM«á¦V¤¤¶¡ªºªÅ®æ¦V¤W¶K»ô©O?

¥»©«³Ì«á¥Ñ vpower ©ó 2010-7-25 00:02 ½s¿è

http://naturefruit.myweb.hinet.net/ask1.xls


¦@¦³¨â­Ó°Ý¦V...

°Ý¦V1:
Sheet1­nÅܦ¨Sheet2
§â²Ä4¦C°Å¤U¶K¨ì²Ä2¦C.²Ä6¦C°Å¤U¶K¨ì²Ä3¦C.²Ä9¦C°Å¤U¶K¨ì²Ä4¦C.²Ä10¦C°Å¤U¶K¨ì²Ä5¦C
(¥u¯à¥Î°Å¤U.¤£¯à¥Î§R°£ªÅ¥Õ¦C.­n¦p¦ó°µ©O?)
´N¦n¹³ http://naturefruit.myweb.hinet.net/ask2.xls ªº"­ì½Z"ªí¤@¼Ë.¥u¯à°Å¤U¦V¤W¶K.¤£¯à§R°£¦C.·|ÄY­«¿ù»~!
°Ý¦V2:
Sheet3­nÅܦ¨Sheet5
¬O§_¥i¥H§PÂ_¨ìAÄæ¬OªÅ­È(ª`·N:Àx¦s®æ¤º¦³¨ç¼Æ¤£ª¾¬O§_·|¼vÅT§PÂ_)
´N¦n¹³ http://naturefruit.myweb.hinet.net/ask2.xls ªº"¤ÀÃþ"ªí¤@¼Ë.¥u­n§PÂ_¨ìAÄæÀx¦s®æ¤ºªº¨ç¼Æ,¹Bºâ¥X¨Óªº­È¬OªÅ­È,´Nª½±µ§R°£¸Ó¦C

¥H¤W°Ý¦V¤w©ó7¤ë24¤é¦­¤W9ÂI36¤À­×¥¿

2¼Óªº¤j¤j¤w¸g«Ü±µªñ¤F(¯uªº«Ü·PÁÂ).³Â·Ð¦U¦ì¤j¤jÅo!

¥»©«³Ì«á¥Ñ kimbal ©ó 2010-7-23 00:13 ½s¿è

°ÝÃD¤@: (³o­Ó¬O¥Î§R°£§a)
  1. on error resume next
  2. ActiveSheet.UsedRange.SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp
½Æ»s¥N½X
°ÝÃD¤G:
¦phsieh¤j¤U­±©Ò»¡,¦pªG¨S¦³¼hÅ|Ãö«Y, ¥H¤U¥i§ä¥X¹B¦æ®É¬OªÅ¦ÊªºµM«á§R°£
  1.     Dim r, rs
  2.     Dim allR As Range
  3.    
  4.     With ActiveSheet.UsedRange
  5.         Set r = .Find("", LookIn:=xlValues)
  6.         Set rs = r
  7.         If Not r Is Nothing Then
  8.            Set allR = r
  9.             Do
  10.                 Set allR = Union(allR, r)
  11.                 Set r = .FindNext(r)
  12.             Loop While Not r.Address = rs.Address
  13.         End If
  14.         If Not allR Is Nothing Then
  15.             allR.Delete Shift:=xlUp
  16.         End If
  17.     End With
½Æ»s¥N½X
À´±oµo°Ý,µª®×´N·|¦b¨ä¤¤

¤µ¤éの¤@¬íは  ©ú¤éにない
http://kimbalko-chi.blogspot.com
http://kimbalko.blogspot.com

TOP

¦^´_ 1# vpower


    °ÝÃD2­Y¥Î½Æ»s/¿ï¾Ü©Ê¶K¤W/­È
¦A§R°£ªÅ®æ¥i¦æ¶Ü?
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

©Î³\¤]¥i¥H±Ä¥Î¥ý²M°£ªÅ¥Õ¦æ¤ºªº¤½¦¡, (¦]¬°¥Î¤£¨ì)
¦A§Q¥Î RangeªºCopy¨ç¼Æ¨Ì§Ç±N¤½¦¡·h²¾¤W¨Ó.
³o¸Ì½Ð®¤¤£¦A¦C¥X¦p¦ó§äªÅ¥Õ¦æªº¤½¦¡.

With Worksheets("Sheet3")
  .Range("A2:J3").Clear
  .Range("A5:J6").Clear
  .Range("A8:J9").Clear

  .Range("A4:J4").Copy _
      Destination:=.Range("A2")
  .Range("A4:J4").Clear


  .Range("A7:J7").Copy _
      Destination:=.Range("A3")
  .Range("A7:J7").Clear

  .Range("A10:J10").Copy _
      Destination:=.Range("A4")
  .Range("A10:J10").Clear

End With

TOP

¦^´_ 4# luhpro
¼Ó¥D»¡©ú¬O¦¹¨Ò¸û³W«h©Ò¥H¾ã¦C§R°£¨Ã¤£¼vÅT
­Y³o¨Ç¤½¦¡±o¨ì«DªÅ­È®É§R°£¤½¦¡´N¤£¹ï¤F
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

http://naturefruit.myweb.hinet.net/ask2.xls

¥H¤WÀɮ׬°·¥¾÷±K.©Ò¥H§Ú§â¤º®e­×§ï¹L¤F.©Ò¥HÀ³¸Ó¥i¥H½Ð¤j¤j­Ì¬Ý¬Ý!§¹¦¨·s¼W/­×§ï/§R°£
·s¼W
¦pªG§Ú²{¦b­n§â"­ì½Z"ªíªº¤º®e.³Ì¤U¤è"·s¼W"²Ä151µ§¸ê®Æ.¥Lªº¾Ç®Õ¬O°ê¤p¦n¤F
µM«á¨ì"¤ÀÃþ"ªí¥h.§Ú­ÌÂI¿ï²Ä55¦CµM«á´¡¤J¤@¦C.µM«á§â²Ä54¦C®Ø°_¨Ó¦V¤U¶ñº¡.
µM«á¬É·|¬Ý¨ì§Ú­Ì©Ò·s¼Wªº²Ä151µ§¸ê®Æ.
­×§ï
¦pªG§Ú²{¦b­n§â"­ì½Z"ªíªº¤º®e."­×§ï"²Ä149µ§¸ê®Æ.§â¹q¸Ü§ï¦¨B149149
µM«á¨ì"¤ÀÃþ"ªí¥h.§Ú­Ì¥i¥H¬Ý¨ì²Ä54¦Cªº¹q¸Ü¤]±q­ì¨ÓªºB149³Q­×§ï¦¨B149149
§R°£
¦pªG§Ú²{¦b­n§â"­ì½Z"ªíªº¤º®e."§R°£"²Ä149µ§¸ê®Æ.(§Ú¬Oª½±µ§R°£¦C)
µM«á¨ì"¤ÀÃþ"ªí¥h,·|µo¥ÍÄY­«¿ù»~.§Ú·Q¤F«Ü¤[·Q¨ì¤@­Ó¤èªk.
¨º´N¬O¤£­n§R°£¦C.¥u­n§R°£¤º®e´N¦n.µM«á§â³Ñ¤Uªº150µ§¸ê®Æ°Å¤U¶K¤W³Q§R°£¤º®eªº²Ä149µ§¸ê®Æ¦C.
´N¤£·|µo¥ÍÄY­«¿ù»~¤F.
¦ý¬O¨º¨Ç³Q§Ú§R°£ªº¸ê®Æ¦b"¤ÀÃþ"ªí·|¥X²{ªÅ¥Õ.©Ò¥H§Ú´N·Q¨ìª½±µ§R°£¦C´N¦n¤F.

2¼Óªºµª®×¤w¸g§Ö­n±µªñ§Úªº»Ý¨D¤F.¦ý¬OÁÙ®t¤@ÂIÂI.

TOP

¥»©«³Ì«á¥Ñ luhpro ©ó 2010-7-26 00:14 ½s¿è

¦^´_ 6# vpower

¦pªG¥u¬O³æ¯Âªº­n±N¸ê®Æ©ñ¦b¤@°_,
§Ú¤§«e¦³·Q¨ì¤@ºØ¤èªk¥i¥H¹F¦¨.
(¦]¬°¤§«e¥Îªº¤è¦¡´N¤£¬O¥Î¤½¦¡°µªº, ©Ò¥H¥ý´£¨Ñµ¹§AÀ³«æ¥Î, ¦Ü©ó¥Î¤½¦¡¨Ó¹F¦¨ªº¤è¦¡´N·Ð½Ð¨ä¥L¤H´£¨Ñµ¹§AÅo)

´N¬O¥ý¦b¬YÄæ³Ì¤W¤è­q©w¤@­Ó°ò¼Æ(0 ©Î 1000 ¬Ò¥i),
C1 = 1000
µM«á§Q¥Î¤½¦¡±N¦³¸ê®Æªº¨º¤@¦C¨Ì§Ç½s¸¹,
C2 = IF(A1="",IF(C1<1000,C1+1000,C1),IF(C1<1000,C1+1,C1-1000+1))
C3 ¥H«áª½±µ ¤U©Ô§Y¥i,(½d¨Ò¤¤¤U©Ô¨ì C501, ¦]¬° C1¨Ã¥¼¹ïÀ³¨ì¸ê®Æªº²Ä¤@¦C)
³Ì«á¦A¤@¤@§ì¥X¨ÓÅã¥Ü¸ê®Æ§Y¥i,
D1 = IF(ROW()>$F$1,"",INDIRECT(CONCATENATE("A",MATCH(ROW(),$C$1C$501,0)-1)))
E1 = IF(ROW()>$F$1,"",INDIRECT(CONCATENATE("B",MATCH(ROW(),$C$1C$501,0)-1)))
D2 »P E2 ¥H«á¥çª½¬Ò¤U©Ô§Y¥i,,(½d¨Ò¤¤¤U©Ô¨ì D500, ¦]¬° D1¦³¹ïÀ³¨ì¸ê®Æªº²Ä¤@¦C)

¦Ü©ó­n«ç¼Ë¨Ï¥Î§ì¥X¨Óªº¸ê®Æ¨º´N­n¬Ý§Aªº»Ý¨DÅo.
ask2_a.zip (84.19 KB)

TOP

¦^´_  vpower

¦pªG¥u¬O³æ¯Âªº­n±N¸ê®Æ©ñ¦b¤@°_,
§Ú¤§«e¦³·Q¨ì¤@ºØ¤èªk¥i¥H¹F¦¨.
(¦]¬°¤§«e¥Îªº¤è¦¡´N ...
luhpro µoªí©ó 2010-7-26 00:11



±z¦n.­º¥ý«D±`ÁÂÁ±zªº¥Î¤ß.¦ý¬O§Ú¤£·|¨Ï¥Î­C.½Ð°Ý¦p¦ó¨Ï¥Î©O?
¦]¬°§Ú§R°£"­ì½Z"ªíªº¸ê®Æ¦C.´N·|¾É­P¿ù»~.­ì¦]¤]¤£ª¾¹D±q¦ó¬d°_.

§Ú­«ÂI¬O©ñ¦b"¤ÀÃþ"ªí
µM«á"ºî¦X"ªí¥u¬OÀ°¥L¤ÀÃþ¥Îªº.¤£¬O¥D­nÅý§Ú¬Ýªº.

TOP

¥»©«³Ì«á¥Ñ luhpro ©ó 2010-7-27 00:21 ½s¿è

§Ú­è­è·Q¨ì¥i¥H¥Î¤½¦¡®M¥Î§Ú¦b²Ä 7 ½g¤W©Ò¨Ï¥Îªº¤èªk,
´N¬O¥Îµ{¦¡¦b "­ì½Z" ªº NOPQRST µ¥Äæ¤À§O¦C¥X¦U¯Å¾Ç®Õ¦WºÙ¦p "¦«¨à©Ò"¡B"¤p¾Ç"...µ¥¨Ì¦¸ªºÃöÁä¦r,
¦A©ó¦UÄæ±N¨Cµ§¸ê®Æ§ì¥X¨Ó¨Ì§Çµ¹¤©½s¸¹,
¥H¤Wµ{¦¡¦p¤U:

Sub Main()
  Dim iCount As Integer, iCol As Integer, iFrow As Integer, iRow As Integer
  Dim iI As Integer, iJ As Integer
  Dim rC As Range

  With Sheets("­ì½Z")
    With .Range("a1:m1")
      Set rC = .Find("¾Ç®Õ", LookIn:=xlValues)
      If Not rC Is Nothing Then
        iCol = rC.Column
      End If
    End With
    iCount = .Cells(Rows.Count, iCol).End(xlUp).Row()

    .Range(Cells(2, 14), Cells(iCount, 20)).Clear

    For iI = 1 To 7
      With .Range(Cells(1, iCol), Cells(iCount, iCol))
        Set rC = .Find(Cells(1, 13 + iI), LookIn:=xlValues)
        If Not rC Is Nothing Then
          iJ = 0
          iFrow = rC.Row
          Do
            iJ = iJ + 1
            iRow = rC.Row
            Cells(iRow, 13 + iI) = iJ
            Set rC = .FindNext(rC)
          Loop While Not rC Is Nothing And rC.Row <> iFrow
        End If
      End With
    Next iI
  End With
End Sub

ask2_a2.zip (75.51 KB)

¦p¦¹¬Æ¦Ü¾ã­Ó "¤ÀÃþ" Sheet ªº¤º®e»P¤å¦r®æ¦¡µ¥³£¥i¥H¥Îµ{¦¡ª½±µ²£¥Í¥X¨Ó. (¥u­n¨S§R¨ì²Ä¤@¦CªºÄæ¦ì¦WºÙ,À³¸Ó³£¤£¨ü­ì½Z½s­×ªº¼vÅT)
¦]¬°®É¶¡±ß¤F¨Ó¤£¤ÎÄ~Äò¤U¥h³o³¡¥÷´N­n½Ð§A¦Û¤v°µ©Îµ¥§Ú±ß¤W¦³®É¶¡¤~¯à°µÅo.

TOP

¨ÌµM¤£À´±z­nªí¹Fªº¬O¤°»ò.

¥ý§i¤@¬q¸¨¦n¤F.§Ú¦³ÂI¥´ºâ©ñ±ó.§ï§Oªº¼gªk¤F.ÁÂÁ±zªº¥Î¤ß

TOP

        ÀR«ä¦Û¦b : ¨ü¤HÂI¤ô¤§®¦¡A¶··í´é¬u¥H³ø¡C
ªð¦^¦Cªí ¤W¤@¥DÃD