GBKEE

¦^´_ 1# jackson7015
§A»¡ªº¦³ªº¨Ç¼Ò½k,¤W¶ÇÀÉ®×»¡²M·¡§a.

GBKEE

¦^´_ 3# jackson7015
¸Õ¸Õ¬Ý

1. Sub Ex()
   
2.     Dim Ar(), Rng As Range, Xi As Integer
   
3.     With Sheets("¤é³øªí")
   
4.         Set Rng = .Range("d7", .[d7].End(xlDown))  '¸ê®Æ½d³ò: BÄ榳®Æªº¦C
   
5.         ReDim Ar(1 To Rng.Count, 1 To 10)          '°}¦Cªº¤j¤p 1 To 10 => ¸ê®Æ½d³ò BÄæ:KÄæ
   
6.         For Xi = 1 To Rng.Count
   
7.             Ar(Xi, 1) = Date                       '¤é´Á
   
8.             Ar(Xi, 2) = .Cells(Rng(Xi).Row, "B")   '½s¸¹
   
9.             Ar(Xi, 3) = .Cells(Rng(Xi).Row, "N")   '³Æµù
   
10.             Ar(Xi, 4) = .Cells(Rng(Xi).Row, "D")   '¦aÂI
    
11.            S = "=IF(RC[4]=1,""¬dµL"",IF(RC[3]=1,""¦¨®×"" & SUM(RC[6]:RC[9])&""KW"",""""))"
    
12.             Ar(Xi, 5) = S                          '¦¨®×
    
13.             Ar(Xi, 6) = .Cells(Rng(Xi).Row, "G")
    
14.             Ar(Xi, 7) = .Cells(Rng(Xi).Row, "H")
    
15.             Ar(Xi, 8) = .Cells(Rng(Xi).Row, "I")
    
16.             Ar(Xi, 9) = .Cells(Rng(Xi).Row, "J")
    
17.           '  Ar(Xi, 10) = .Cells(Rng(Xi).Row, "n")  '  ** ½Ð°Ý³o¸Ì ­n¼g¨Ç¤°»ò?   **
    
18.         Next
    
19.     End With
    
20.     With Sheets("ºî¦X¸ê®Æ®w")
    
21.         .Range("B5:O" & Rows.Count) = ""        '²M°£ ¸ê®Æ
    
22.         .[B5].Resize(Rng.Count, 10) = Application.Transpose(Application.Transpose(Ar))
    
23.                                                'Âà¸m°}¦C  ¶ñ¤J:¸ê®Æ
    
24.     End With
    
25. End Sub

½Æ»s¥N½X

GBKEE

¦^´_ 5# jackson7015

1.     With Sheets("ºî¦X¸ê®Æ®w").Cells(Rows.Count, "B").End(xlUp).Offset(1)
   
2.          .Resize(Rng.Count, UBound(AR, 2)) = Application.Transpose(Application.Transpose(AR))
   
3.     End With

½Æ»s¥N½X

[   ]   ¬Ý³o¸Ì ...

GBKEE

¦^´_ 8# jackson7015
¥i¥H£«  ¤£¹L ¤é³øªí B3=TODAY()   ÁÙ¬O·í¤Ñªº¤é´Á

GBKEE

¦^´_ 10# jackson7015
Ar(Xi, 1) = .[B3]                       '¤é´Á
¡A³Ñ¤Uªº¨º­Ó³rÂI¦A¦n¦n¬ã¨s

1. With [10].Font
   
2.         .Name = "·s²Ó©úÅé"
   
3.         .FontStyle = "¼Ð·Ç"
   
4.         .Size = 11
   
5.         .Strikethrough = False
   
6.         .Superscript = False
   
7.         .Subscript = False
   
8.         .OutlineFont = False
   
9.         .Shadow = False
   
10.         .Underline = xlUnderlineStyleNone
    
11.         .ColorIndex = 1
    
12.     End With

½Æ»s¥N½X

GBKEE

¦^´_ 13# jackson7015
¶ÇÀɬݬÝ

GBKEE

¦^´_ 15# jackson7015
Xi As Integer  ³o¸Ì«Å§i Integer ÅܼƫY¥H½d³ò¬° -32,768 ¨ì 32,767 ¤§ 16 ¦ì¤¸ (2 ­Ó¦ì¤¸²Õ) ¼Æ¦rªº§Î¦¡Àx¦s
­×§ï¬°
Xi As Long   Long (ªø¾ã¼Æ)ÅܼƫY¥H½d³ò±q -2,147,483,648 ¨ì 2,147,483,647 ¤§ 32 ¦ì¤¸ (4 ­Ó¦ì¤¸²Õ) ¦³¸¹¼Æ¦r§Î¦¡Àx¦s¡CLong ªº«¬ºA«Å§i¦r¤¸¬° &¡C

Sub ²Ö­p¤é³øªí¸ê®Æ()
  '  *** If MsgBox("¬O§_°õ¦æ½Æ»s¡H", vbYesNo) = vbNo Then Exit Sub  ²¾¨ì¤U¤è
    Dim Ar(), Rng As Range, Xi As Long
    With Sheets("¤é³øªí")
        Set Rng = .Range("d7", .[d7].End(xlDown))  '¸ê®Æ½d³ò: BÄ榳¸ê®Æªº¦C
          ' *** ¥[¤W§PÂ_¤é³øªí ¨S¦³¸ê®Æ  ****
        If Application.CountA(Rng) = 0 Then MsgBox "¤é³øªí ¨S¦³¸ê®Æ !!!": Exit Sub   
        If MsgBox("¬O§_°õ¦æ½Æ»s¡H", vbYesNo) = vbNo Then Exit Sub
        ReDim Ar(1 To Rng.Count, 1 To 20)          '°}¦Cªº¤j¤p 1 To 20 => ¸ê®Æ½d³ò BÄæ:VÄæ
        For Xi = 1 To Rng.Count     '<-¬O³o¸Ì¿ù»~  Xi As Integer
'¤é³øªí ¨S¦³¸ê®Æ  Rng.Count =Rows.Count-7 : 2003ª© 65,536 - 7  > 32,767

GBKEE

¦^´_ 17# jackson7015

1. With Sheets("¤é³øªí")
   
2.         If .Cells(Rows.Count, "D").End(xlUp).Row = 4 Then   '°»¬d¬O§_¸ê®Æ
   
3.             MsgBox "¤é³øªí  ¨S¦³¸ê®Æ !!"
   
4.             Exit Sub
   
5.         End If
   
6.         Set Rng = .Range("d7", .Cells(Rows.Count, "D").End(xlUp)) '  **** ³o¸Ì§ï¦¨ ¥Ñ¤U©¹¤W  ¸ê®Æ½d³ò: BÄ榳¸ê®Æªº¦C
   
7.         If Application.CountA(Rng) = 0 Then MsgBox "¤é³øªí ¨S¦³¸ê®Æ !!!": Exit Sub '§PÂ_¤é³øªí¦³¨S¦³¸ê®Æ
   
8.         If MsgBox("¬O§_°õ¦æ½Æ»s¡H", vbYesNo) = vbNo Then Exit Sub

½Æ»s¥N½X

GBKEE

¦^´_ 19# jackson7015
§A»¡ªº¦³²z ¨º´N¬O¦h¾l¤F