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

[µo°Ý] ¦p¦ó¨Ï¥Î¥¨¶°·s¼W¤@µ§¸ê®Æ?

[µo°Ý] ¦p¦ó¨Ï¥Î¥¨¶°·s¼W¤@µ§¸ê®Æ?

¥»©«³Ì«á¥Ñ yeh199200 ©ó 2011-9-29 10:21 ½s¿è

TEST.zip (835.58 KB) ³o¬O¤p§ÌÆ[¹î¦U°êªÑ¥«ªºexcel
¨C¤Ñ³£­n§ó·s¸ê®Æ ı±o«Ü§xÂZ.
§ÚÁ|¿D¬w¬°¨Ò(¹Ï1)
¥ý½Æ»s9/26(Äæ¦ì550) ¶K¦Ü(Äæ¦ì551)
±Na551§ï¬°9/27 µM«á±Nb551§ï¬° =¼Æ¾Ú!C$4,c551§ï¬° =¼Æ¾Ú!D$4,D551§ï¬° =¼Æ¾Ú!E$4,E551§ï¬° =¼Æ¾Ú!F$4
µM«á¨C­Ó°ê®a¥þ³¡³£­n§ó·s
¤p§Ì·Q°Ýªº¬O ¦p¦ó¨Ï¥Î¥¨¶°·s¼W£¸¤ñ¸ê®Æ??

¦^´_ 1# yeh199200
§A¸Õ¬Ý¬Ý³o¬O¤£¬O§A­nªº :
  1. Sub nn()
  2.   Dim lCurRows As Long, lJ As Long
  3.   Dim rData As Range
  4.   
  5.   Set rData = Sheets("¼Æ¾Ú").Cells(1, 1)
  6.   
  7.   With rData
  8.     For lJ = 4 To 17
  9.       If .Cells(lJ, 1) <> "" And .Cells(lJ, 3) <> "" Then
  10.         With Sheets(CStr(.Cells(lJ, 1)))
  11.           lCurRows = .Cells(Rows.Count, 1).End(xlUp).Row
  12.           .Cells(lCurRows, 1).Resize(1, 10).Copy
  13.           .Cells(lCurRows + 1, 1).PasteSpecial
  14.           .Cells(lCurRows + 1, 1) = Date ' ¤µ¤Ñ¤é´Á
  15.    
  16.           rData.Parent.Cells(lJ, 3).Resize(1, 4).Copy
  17.           .Cells(lCurRows + 1, 2).PasteSpecial Paste:=xlPasteValues
  18.         End With
  19.       End If
  20.     Next lJ
  21.   End With
  22. End Sub
½Æ»s¥N½X

TOP

¦^´_ 2# luhpro

ÁÂÁ¦^ÂÐ ¤£¹L¦n¹³¦³ÂI©Ç©Çªº.
§Ú·Qªº¬O ­n³æ¤@°ê®a(¿D¬w)´N¦n,¦]¬°¤£¬O¨C¤Ñ³£·|¦³¥æ©ö.±z¼gªº¬O ¨È¬w³£¥[¤J.
³o¬O§Ú¿ý»sªº¿D¬w·s¼Wªº¥¨¶°
Sub auadd()
    Rows("551:551").Select
    Selection.Copy
    Range("A552").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=TODAY()"
    Range("B552").Select
    ActiveCell.FormulaR1C1 = "=¼Æ¾Ú!R4C[1]"
    Range("B552").Select
    Selection.Copy
    Range("C552:E552").Select
    ActiveSheet.Paste
End Sub
¥i§_­×§ï¦¹¥¨¶°ÁÂÁÂ

TOP

¦^´_ 3# yeh199200
  1. Sub auadd()
  2.   Dim lCurRows As Long ' ¦C¸¹³Ì¦h 65535(©Î 65536) ¾ã¼Æ©ñ¤£¤U(-32,768 ¨ì 32,767), ©Ò¥H¥Îªø¾ã¼Æ
  3.   
  4.   With Sheets("¿D¬w") ' ¥H¤Uªº . µ¥¦P Sheets("¿D¬w") ª½¨ì³Ì©³¤Uªº¤@­Ó End With
  5.     lCurRows = .Cells(Rows.Count, 1).End(xlUp).Row ' §ä¨ì³Ì©³¦C (©³¤Uµù¸Ñ¤¤¥H ¥Ò ¥Nªí³Ì©³¦C¸¹, ¨Ò¦p : ²Ä ¥Ò <551> ¦C)
  6.     .Rows(lCurRows).Copy ' Copy ¥Ò ¾ã¦C
  7.     lCurRows = lCurRows + 1 ' ¥Ò = ¥Ò + 1, §Y¸õ¨ì¤U¤@¦C·Ç³Æ³B²z
  8.     With .Cells(lCurRows, 1) ' ¥H¤Uªº . µ¥¦P A¥Ò(A552) Àx¦s®æ
  9.       .PasteSpecial ' ¶K¤W¾ã¦C
  10.       .Value = Date ' ¥Ò ¦C A Ä椺®e³]¬°¤µ¤Ñ
  11.       Sheets("¼Æ¾Ú").Cells(4, 3).Resize(1, 5).Copy ' Copy ¿D¬w ¸ê®Æ¦C¤¤¨º 5 ®æªº¸ê®Æ
  12.       With .Offset(0, 1) ' ©³¤Uªº . µ¥¦P A¥Ò Àx¦s®æ¥k²¾¤@®æ (§Y B¥Ò) Àx¦s®æ
  13.         .PasteSpecial ' ¶K¤W¤§«e Copy ªº 5 ®æ¸ê®Æ
  14.         .Resize(1, 5).Interior.ColorIndex = xlNone ' ²M°£¤§«e¶K¤Wªº¨º¨ÇÀx¦s®æªº©³¦â
  15.       End With
  16.       .Select ' µJÂI²¾¨ì A¥Ò ¦ì¸m¤W (§Y¦s©ñ ¤µ¤Ñ¤é´Á ªºÀx¦s®æ)
  17.     End With
  18.   End With
  19. End Sub
½Æ»s¥N½X

TOP

¦^´_  yeh199200
luhpro µoªí©ó 2011-9-30 22:58


ya ÁÂÁ±z
¤£¹L§Ú·Q­nB533¶ñ¤Jªº¬O"=¼Æ¾Ú!C4",C533¶ñ¤Jªº¬O"=¼Æ¾Ú!D4",E533¶ñ¤Jªº¬O"=¼Æ¾Ú!E4",F533¶ñ¤Jªº¬O"=¼Æ¾Ú!F4"
³o¼Ë·í§Ú¥´¶}sheet1 ªº®É­Ô ´N¥i¬Ý¨ì¥þ³¡°ê®aªº§Þ³N«ü¼Ð¸ê®Æ .. ÁÂÁ±z

TOP

¥»©«³Ì«á¥Ñ luhpro ©ó 2011-10-1 09:47 ½s¿è
ya ÁÂÁ±z
¤£¹L§Ú·Q­nB533¶ñ¤Jªº¬O"=¼Æ¾Ú!C4",C533¶ñ¤Jªº¬O"=¼Æ¾Ú!D4",E533¶ñ¤Jªº¬O"=¼Æ¾Ú!E4",F533¶ñ¤Jªº¬O"=¼Æ¾Ú!F4"
yeh199200 µoªí©ó 2011-10-1 08:23

¬Ý¤£À´§Aªº·N«ä©O.
»Ý­nÂç²Mªº¤@¨Ç¨Æ¶µ :
1. ¬O§_¨C¤Ñ³£¦³·s¸ê®Æ, ·s¸ê®Æ¬O§_³£¬O­n¦b¨C¤@­Ó Sheet ªº³Ì©³ºÝ·s¼W¤@¦C¦s©ñ? ÁÙ¬O¨C¦¸³£©ñ¦b©T©w¦C (¦C¸¹ 533 ¤£¬O 553) ¤W?
2. ¬O§_¬O­n±N Sheets("¼Æ¾Ú") ªº¬Y¦C¤¤ C Äæ ¨ì F Äæ ¸ê®Æ©ñ¨ì ·s¼W¨º¦Cªº B Äæ ¨ì E Äæ?
3. ¬O§_¨S¸ê®Æ´N¤£·s¼W¤@¦C, ©Î¬O·Ó¼Ë­n·s¼W¤@¦C¨S¸ê®Æªº?
¦]¬°§A­ì¥ýªº¨Ò¤l¬O½d³òÀx¦s®æ½Æ»s«á¶K¤W, ( B ~ E)
¦ý¤W¤@½g«oÅܦ¨¶¡¹j©ñ¸m. (B~C, E~F)

TOP

¬Ý¤£À´§Aªº·N«ä©O.
»Ý­nÂç²Mªº¤@¨Ç¨Æ¶µ :
1. ¬O§_¨C¤Ñ³£¦³·s¸ê®Æ, ·s¸ê®Æ¬O§_³£¬O­n¦b¨C¤@­Ó Sheet ªº³Ì ...
luhpro µoªí©ó 2011-10-1 09:45

¤£¦n·N«ä ¤p§Ì¥i¯à¨S»¡²M·¡
1. ¬O§_¨C¤Ñ³£¦³·s¸ê®Æ, ·s¸ê®Æ¬O§_³£¬O­n¦b¨C¤@­Ó Sheet ªº³Ì©³ºÝ·s¼W¤@¦C¦s©ñ?
¹ïªº. ¥u­n·s¼W¨ì³Ì«áSheet ªº³Ì©³ºÝ·s¼W¤@¦C¦s©ñ
2. ¬O§_¬O­n±N Sheets("¼Æ¾Ú") ªº¬Y¦C¤¤ C Äæ ¨ì F Äæ ¸ê®Æ©ñ¨ì ·s¼W¨º¦Cªº B Äæ ¨ì E Äæ?
¤p§Ì ¤£¬O­n¶ñ¤J¼Æ­È,¬O­nB533 ¶ñ¤J =¼Æ¾Ú!C4
                                      C533¶ñ¤J  =¼Æ¾Ú!D4
                                      D533¶ñ¤J =¼Æ¾Ú!E4
                                      F533¶ñ¤J = ¼Æ¾Ú!F4

´N¬O³o¼Ëº¸¤w.
3¬O§_¨S¸ê®Æ´N¤£·s¼W¤@¦C, ©Î¬O·Ó¼Ë­n·s¼W¤@¦C¨S¸ê®Æªº?
¥u­n·s¼W¨ì³Ì«á¤@¦C´N¦n..

ÁÂÁ±z

TOP

¦^´_ 7# yeh199200
¬Ý°_¨Ó§Aªº·N«ä¦ü¥G¬O­n·s¼W¤½¦¡.
³o¼Ë¥u­n±N¤W­zµ{¦¡¤¤²Ä 11 ~ 15 ¦æ§ï¦¨ :
      For iI = 1 To 4
        .Offset(0, iI).FormulaLocal = "=¼Æ¾Ú!" & Chr(66 + iI) & "4"
      Next iI
§Y¥i.

¦ý¬O¦]¬°Àx¦s®æ¤º­Y¥Î¤½¦¡¨ä­È¬O·|ÀHµÛ­ì©l¸ê®Æ¦ÓÅܧó,
¤]´N¬O¤£ºÞ§A·s¼W¦h¤Ö­ÓÀx¦s®æ¥X¨Ó,
©Ò¦³Àx¦s®æ¤¤¥u­n¤½¦¡¬Û¦Pªº¨ä­È³£·|¬O§¹¥þ¬Û¦Pªº³á:



³o¯u¬O§A­nªºµ²ªG¶Ü?

TOP

¦^´_  yeh199200
¬Ý°_¨Ó§Aªº·N«ä¦ü¥G¬O­n·s¼W¤½¦¡.
³o¼Ë¥u­n±N¤W­zµ{¦¡¤¤²Ä 11 ~ 15 ¦æ§ï¦¨ :
      Fo ...
luhpro µoªí©ó 2011-10-1 16:29

ÁÂÁ±z ¤p§Ì´N¬O­n¦¹µ²ªG ÁÂÁÂ
¯uªº¸Ñ¨M¤p§Ì¦n´X­Ó¤ëªº°ÝÃD...ÁÂÁÂ

TOP

        ÀR«ä¦Û¦b : ¤Ó¶§¥ú¤j¡B¤÷¥À®¦¤j¡B§g¤l¶q¤j¡A¤p¤H®ð¤j¡C
ªð¦^¦Cªí ¤W¤@¥DÃD