eigen

¦^´_ 3# GBKEE


ÁÂÁ¤j¤jªº¤j¤OÀ°¦£¡A§Ú¸ÕµÛ­×§ï¡A¥Ø«e¤w¸g¡y¤j­P¡zok¤F¡A

§Ú°Ñ¦Ò§Aªº½d¨Ò¡A­×§ïµ{¦¡

¦pªþ¥ó
https://dl.dropboxusercontent.com/u/12575824/%E8%A1%8C%E4%BA%8B%E6%9B%86.xls

1. Option Explicit
   
2. '¤u§@ªí¼Ò²Õ:ÂùÀ»Àx¦s®æµ{¦¡
   
3. Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   
4. Dim aa As Integer
   
5. 
   
6.         Cells(7, 2).Value = Target.Cells(1, 1)                'copy to B2
   
7.         'Cells(7, 2).NumberFormatLocal = "G/³q¥Î®æ¦¡"
   
8. 
   
9.         Ex Cells(7, 2), Target.Parent
   
10. 'Target.Select
    
11. End Sub
    
12. 
    
13. Private Sub Ex(Rng As Range, sh As Worksheet)
    
14.         Dim E As Range
    
15. 
    
16.         If Rng = "" Then Exit Sub
    
17.         If IsNumeric(Rng) Then
    
18.                 Sheets("Sheet1").Activate
    
19.                 If Selection.Row > 1 And Selection.Row < 52 And Selection.Column >= 6 And Selection.Column <= 7 Then
    
20.                         Selection.Value = Rng
    
21.                 End If
    
22.         End If
    
23. End Sub

½Æ»s¥N½X

¥[¨ì sheet ¤ë¾ä¤§¤¤¡A¨Ã¦b sheet1 ¤§¤¤¥[¤J

1. Option Explicit
   
2. '¤u§@ªí¼Ò²Õ:ÂùÀ»Àx¦s®æµ{¦¡
   
3. Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   
4.         If Target.Cells(1).Row > 1 And Target.Cells(1).Row < 52 And Target.Cells(1).Column >= 6 And Target.Cells(1).Column <= 7 Then
   
5.                 Sheets("¤ë¾ä").Activate
   
6.         End If
   
7. End Sub

½Æ»s¥N½X

¦p¦¹¤@¨Ó¡A§Ú«K¥i¥H¦b¨â­Ósheet ¤¤¡A¥Î ÂùÀ»°µ¤Á´«¡C¡]³o³¡¥÷¥\¯àok¤F¡A¤Q¤G¸U¤Àªº·PÁ¡^

²{¦b§Ú¤£¤F¸Ñªº¬O

¤@¡B­ì¥»´£¨Ñªºµ{¦¡¬O

Ex Target.Cells(1), Target.Parent

³o­Ó Target.Cells(1) §Ú«ç»ò§ï¡A³£§ï¤£¥X­ì©l­È¡]³£¬O¸g¹L®æ¦¡¤Æªº¸ê®Æ¡^¡A³Ì«á§Ú¨S§é¡A¥u¦n±N­È¼g¨ìb2 ¡A¦A±Nb2 ªº­È¥á¥X¨Ó¡]¬Ý¤£¨ì­È¬O¦]¬°¦r«¬ÃC¦â§Ú½Õ¾ã¥Õ¦â¡^

¤G¡B
­pºâ¤é´Á
=if(        month(OFFSET($B$2,QUOTIENT(ROW()-4,9)*9,QUOTIENT(COLUMN()-2,8)*8,,))<>
        month(OFFSET($B$2,QUOTIENT(ROW()-4,9)*9,QUOTIENT(COLUMN()-2,8)*8,,)-(weekday(        OFFSET($B$2,QUOTIENT(ROW()-4,9)*9,QUOTIENT(COLUMN()-2,8)*8,,),1)-1)+ MOD(ROW()-4,9)*7 +MOD(COLUMN()-2,8)),
        "",
        OFFSET($B$2,QUOTIENT(ROW()-4,9)*9,QUOTIENT(COLUMN()-2,8)*8,,)-(weekday(        OFFSET($B$2,QUOTIENT(ROW()-4,9)*9,QUOTIENT(COLUMN()-2,8)*8,,),1)-1)+ MOD(ROW()-4,9)*7 +MOD(COLUMN()-2,8))
³o­Ó¼ö¼öªøªºµ{¦¡¡A¦³¾÷·|ÁYµu¶Ü¡H ¡]¨Ò¦p¥Î ¦WºÙ ©w¸q¡^

GBKEE

¦^´_ 2# eigen
¸Õ¸Õ¬Ý

1. Option Explicit
   
2. '¤u§@ªí¼Ò²Õ:ÂùÀ»Àx¦s®æµ{¦¡
   
3. Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   
4.     Ex Target.Cells(1), Target.Parent
   
5.     Target.Select
   
6. End Sub
   
7. 
   
8. Private Sub Ex(Rng As Range, sh As Worksheet)
   
9.     Dim T_Date, T_Mon As Integer, TE As Date
   
10.     Dim M As Integer, Week_Ar(), I As Integer
    
11.     Week_Ar = Array("¤é", "¤@", "¤G", "¤T", "¥|", "¤­", "¤»")
    
12.     Do Until IsDate(T_Date)
    
13.         T_Date = InputBox("½Ð«ü©w¤é´Á", , Date)
    
14.         If T_Date = "" Then Exit Sub
    
15.     Loop
    
16.     With sh
    
17.         .Cells.Clear                            '²M°£¤u§@ªí
    
18.         .Cells.HorizontalAlignment = xlCenter   'Àx¦s®æª«¥óªº¤ô¥­¹ï»ô¼Ò¦¡:¸m¤¤
    
19.         .Cells.VerticalAlignment = xlCenter     'Àx¦s®æª«¥óªº««ª½¹ï»ô¼Ò¦¡:¸m¤¤
    
20.     End With
    
21.    
    
22.     With Rng.Resize(, 7)                        '«ü©w¤é´Áªº®æ¦¡
    
23.         .Merge                                  '¦X¨Ö: .Resize(, 7)
    
24.         .Font.Size = 30
    
25.         .Font.Bold = True
    
26.         .Font.Color = vbBlue
    
27.         .NumberFormatLocal = "yyyy-m-d"
    
28.          Rng = T_Date                           '¤é´Á
    
29.     End With
    
30.     I = I + 3
    
31.     For M = -1 To 1
    
32.         T_Date = DateAdd("M", M, Rng.Cells(1))                           '¨ú±o«ü©w¤ë¥÷ªº¤é´Á
    
33.         T_Date = DateSerial(Year(T_Date), Month(T_Date), 1)     '«ü©w¤ë¥÷¤é´Áªº 1¸¹¶}©l
    
34.         T_Mon = Month(T_Date)                                   '«ü©wªº¤ë¥÷
    
35.         With Rng.Cells(I).Resize(1, 7)                          '¼g¤J «ü©w¤ë¥÷ªº®æ¦¡
    
36.             .Merge
    
37.             .Cells = T_Date
    
38.             .NumberFormatLocal = "yyyy-m"
    
39.             .Font.Size = 15
    
40.             .Font.Bold = True
    
41.             .Font.Color = vbWhite
    
42.             .Interior.Color = vbBlue
    
43.         End With
    
44.         I = I + 1
    
45.         With Rng.Cells(I).Resize(, 7)                           '¼g¤J¬P´Áªº®æ¦¡
    
46.             .Cells = Week_Ar
    
47.             .Interior.ColorIndex = 15
    
48.             .Range("A1,G1").Font.Color = vbRed
    
49.         End With
    
50.         I = I + 1
    
51.         Do While T_Mon = Month(T_Date)                          '°j°éªº±ø¥ó:¦P¤@­Ó¤ë¥÷
    
52.             With Rng.Cells(I, Weekday(T_Date))
    
53.                 .Cells = T_Date
    
54.                 .NumberFormatLocal = "D"
    
55.                 If Weekday(T_Date) = 1 Or Weekday(T_Date) = 7 Then .Font.Color = vbRed
    
56.             End With
    
57.             If Weekday(T_Date) = 7 And T_Mon = Month(T_Date + 1) Then I = I + 1
    
58.             T_Date = T_Date + 1                                 '¤é´Á¥[¤@¤Ñ
    
59.         Loop
    
60.         I = I + 2
    
61.     Next
    
62. End Sub

½Æ»s¥N½X

eigen

¦pªþ¥ó
https://dl.dropboxusercontent.com/u/12575824/%E8%A1%8C%E4%BA%8B%E6%9B%86.xls

¨C­Ó¤ëªº²Ä¤@¤Ñ¡A§Ú¥Î =eomonth(TODAY(),quotient(COLUMN()-2,8)+quotient(ROW()-2,9)*4-5)+1 ¸Ñ¨M¤F

¨C­Ó¤ëªº¨C¤@¤Ñ¡A§Ú¥Î
                OFFSET($B$2,QUOTIENT(ROW()-4,9)*9,QUOTIENT(COLUMN()-2,8)*8,,)
-(weekday(        OFFSET($B$2,QUOTIENT(ROW()-4,9)*9,QUOTIENT(COLUMN()-2,8)*8,,),1)-1)
+ MOD(ROW()-4,9)*7
+MOD(COLUMN()-2,8)

¸Ñ¨M¤F
¬°¤F­n¦n¬Ý¡A¤S¥[¤W¤F
=if(        month(OFFSET($B$2,QUOTIENT(ROW()-4,9)*9,QUOTIENT(COLUMN()-2,8)*8,,))<>
        month(OFFSET($B$2,QUOTIENT(ROW()-4,9)*9,QUOTIENT(COLUMN()-2,8)*8,,)-(weekday(        OFFSET($B$2,QUOTIENT(ROW()-4,9)*9,QUOTIENT(COLUMN()-2,8)*8,,),1)-1)+ MOD(ROW()-4,9)*7 +MOD(COLUMN()-2,8)),
        "",
        OFFSET($B$2,QUOTIENT(ROW()-4,9)*9,QUOTIENT(COLUMN()-2,8)*8,,)-(weekday(        OFFSET($B$2,QUOTIENT(ROW()-4,9)*9,QUOTIENT(COLUMN()-2,8)*8,,),1)-1)+ MOD(ROW()-4,9)*7 +MOD(COLUMN()-2,8))



¡°²{¦bªº°ÝÃD
¤£Ãøµo²{¡A«ü¥O¤Óªø¤F¡A¤S¯ä¤Sªø
OFFSET($B$2,QUOTIENT(ROW()-4,9)*9,QUOTIENT(COLUMN()-2,8)*8,,)-(weekday(        OFFSET($B$2,QUOTIENT(ROW()-4,9)*9,QUOTIENT(COLUMN()-2,8)*8,,),1)-1)+ MOD(ROW()-4,9)*7 +MOD(COLUMN()-2,8)
¤Óªø¤F¡A§Ú­ì¥»·Q­n¥Î ´¡¤J->¦WºÙ->©w¸q ªº¤è¦¡¸Ñ¨M¡A¦ý¬O©w¸q month_first_day -->OFFSET($B$2,QUOTIENT(ROW()-4,9)*9,QUOTIENT(COLUMN()-2,8)*8,,)

¦A±N¨C¤éªº­pºâ¤½¦¡¼g¦¨
=if(        month(        month_first_day)<>
        month(        month_first_day-(weekday(        month_first_day,1)-1)+ MOD(ROW()-4,9)*7 +MOD(COLUMN()-2,8)),
        "",
                month_first_day-(weekday(        month_first_day,1)-1)+ MOD(ROW()-4,9)*7 +MOD(COLUMN()-2,8))

µLªk¨Ï¥Î¡A½Ð«ü±Ð¡B«üÂI¡AÁÂÁÂ