takeshilin88

¦^´_ 6# GBKEE

GBKEE¤j¤j¡G
    §Ú¦b
    .[d1] = Application.Evaluate("sum(" & .[c:c].Address(, , , 1, 1) & ")")
    ªº¤U¤@¦æ³]©w
    .[d1] = Selection.NumberFormatLocal = "#,##0_ "   
    µ²ªG¨C­Ó¤u§@ªíªºd1Àx¦s®æ³£Åܦ¨FALSE¤F¡A
    ½Ð°Ý¬O­þ¸Ì¥X¤F°ÝÃD©O¡H

GBKEE

¦^´_ 5# takeshilin88

¨C­Ó¤u§@ªíªº D1 ) ¡A·Q­n¦³¤d¤À¦ì¼Ë¦¡¡A¨Ò¡G1,234,567
§A¥i¥Î¿ý»s¥¨¶°±o¨ìªºµ{¦¡½X®M¤W
¦Û¦æ¸Õ¸Õ¬Ý

1. .[d1] = Application.Evaluate("sum(" & .[c:c].Address(, , , 1, 1) & ")")
   
2.             .[d1] =????

½Æ»s¥N½X

takeshilin88

¦^´_ 4# takeshilin88

À°¦£³]©w¡G
    ¨C­Ó¤u§@ªíªº¥[Á`ª÷ÃBÀx¦s®æ ( ¨C­Ó¤u§@ªíªº D1 ) ¡A·Q­n¦³¤d¤À¦ì¼Ë¦¡¡A¨Ò¡G1,234,567

½Ð°Ñ¦ÒªþÀÉ¡AÁÂÁÂ

takeshilin88

§ó¥¿¡G

GBKEE¤j¤j¡G
´ú¸Õ¹L«á¥i¥H¨Ï¥Î¤F¡A¦ý¬O¦³¨â¶µ»Ý­n¤p¤p­×§ïªº¦a¤è¡G
1.¨C­Ó¤ë¨ì´Áªº¥[Á`ª÷ÃBÀx¦s®æ ( ¨C­Ó¤u§@ªíªº D1 ) ¡A·Q­n¦³¤d¤À¦ì¼Ë¦¡¡A¨Ò¡G1,234,567
2.¦pªG¦b¡u¨ì´Á¤é¡vDÄæ´¡¤J¤@Äæ¡u»¡©úÄæ¡v¡G§Y¡u»¡©úÄæ¡v¦¨¬°DÄæ¡A¡u¨ì´Á¤é¡v¦¨¬°EÄæ¡A
    §Ú±Nµ{¦¡½XªºD³£§ï¬°E¡A·|¥X²{°}¦C¯Á¤Þ¶W¥X½d³ò.......
   ²Ä2¶µ¤w¸g¸Ñ¨M¤F
½ÐÀ°À°¦£±Ð§Ú¦p¦ó½Õ¾ãµ{¦¡½X¡A
ÁÂÁÂ

takeshilin88

¦^´_ 2# GBKEE

GBKEE¤j¤j¡G
´ú¸Õ¹L«á¥i¥H¨Ï¥Î¤F¡A¦ý¬O¦³¨â¶µ»Ý­n¤p¤p­×§ïªº¦a¤è¡G
1.¨C­Ó¤ë¨ì´Áªº¥[Á`ª÷ÃBÀx¦s®æ ( ¨C­Ó¤u§@ªíªº D1 ) ¡A·Q­n¦³¤d¤À¦ì¼Ë¦¡¡A¨Ò¡G1,234,567
2.¦pªG¦b¡u¨ì´Á¤é¡vDÄæ´¡¤J¤@Äæ¡u»¡©úÄæ¡v¡G§Y¡u»¡©úÄæ¡v¦¨¬°DÄæ¡A¡u¨ì´Á¤é¡v¦¨¬°EÄæ¡A
    §Ú±Nµ{¦¡½XªºD³£§ï¬°E¡A·|¥X²{°}¦C¯Á¤Þ¶W¥X½d³ò...
½ÐÀ°À°¦£±Ð§Ú¦p¦ó½Õ¾ãµ{¦¡½X¡A
ÁÂÁÂ

GBKEE

¦^´_ 1# takeshilin88
¸Õ¸Õ¬Ý

1. Option Explicit
   
2. Dim WB As Workbook, AR(), D As Object
   
3. Sub Main()
   
4.     Ex_yymm
   
5.     Ex_Copy
   
6. End Sub
   
7. Private Sub Ex_yymm()
   
8.     Dim i As Integer, YM As String
   
9.     Set D = CreateObject("SCRIPTING.DICTIONARY") '¦r¨åª«¥ó
   
10.     With Workbooks("A.XLSM").Sheets("¤ä²¼®M¦L")
    
11.         i = 6
    
12.         Do
    
13.             YM = Format(.Cells(i, "D"), "ee/mm")
    
14.             D(YM) = "=AND(YEAR(¨ì´Á¤é)=" & Format(.Cells(i, "D"), "YYYY") & ",MONTH(¨ì´Á¤é)=" & Format(.Cells(i, "D"), "mm") & ")" 'Format(.Cells(i, "D"), "ee/mm")  'YM : ¦r¨åª«¥óªºkey­È (Ū¨ú¤ë¥÷)
    
15.             i = i + 1
    
16.         Loop Until .Cells(i, "D") = ""
    
17.         i = Application.SheetsInNewWorkbook
    
18.         Application.SheetsInNewWorkbook = D.Count + 1
    
19.         Set WB = Workbooks.Add
    
20.         Application.SheetsInNewWorkbook = 1
    
21.         .Copy WB.Sheets(1)
    
22.         WB.Sheets(1).Rows("1:4").Delete
    
23.         WB.Sheets(1).Name = .Name
    
24.     End With
    
25.     AR = D.keys
    
26. End Sub
    
27. Private Sub Ex_Copy()
    
28.     Dim Sh As Worksheet, Rng As Range, i As Integer, xRow As Integer
    
29.     Set Sh = WB.Sheets(1)
    
30.     Set Rng = Sh.Cells(1, Columns.Count).Resize(2)
    
31.     Rng.Cells(1) = "AAA"
    
32.     For i = 0 To UBound(AR)
    
33.         Rng.Cells(2) = D(AR(i))
    
34.         Sh.Range("A:D").AdvancedFilter xlFilterCopy, Rng, WB.Sheets(i + 2).[A2]
    
35.         'AdvancedFilter    ¶i¶¥¿z¿ï    , ¿z¿ï:½Æ»s ,¿z¿ï·Ç«h,   ½Æ»s¨ìªº¦a¤è
    
36.         With WB.Sheets(i + 2)
    
37.             .Name = Replace(AR(i), "/", "_") & " ¨ì´Á"
    
38.             .[A1] = AR(i) & " ¨ì´Á:"
    
39.             .[d1] = Application.Evaluate("sum(" & .[c:c].Address(, , , 1, 1) & ")")
    
40.             .[d1].NumberFormatLocal = "#,##0_ "
    
41.             xRow = WB.Sheets(WB.Sheets.Count).Cells(Rows.Count, "a").End(xlUp).Row
    
42.             If xRow > 1 Then xRow = xRow + 1
    
43.             .Range("a1").CurrentRegion.Copy WB.Sheets(WB.Sheets.Count).Cells(xRow, "A")
    
44.         End With
    
45.     Next
    
46.     Rng.Clear
    
47.     With WB
    
48.         .Sheets(WB.Sheets.Count).Name = "¥Ø«e¤ä²¼ª¬ªp"
    
49.         .SaveAs "D:\B.XLSX"    '¦sÀÉ
    
50.     End With
    
51. End Sub

½Æ»s¥N½X