GBKEE

¦^´_ 1# afu9240

1. TextBox6.Value = Format(Worksheets("¥s­×©ú²Ó").Cells(x, 1) + 1, "000")

½Æ»s¥N½X

GBKEE

1. Private Sub CommandButton4_Click() 'clear
   
2.     TextBox11.Text = ""
   
3.     TextBox10.Text = ""
   
4.     TextBox1.Text = ""
   
5.     Image1.Picture = LoadPicture
   
6.     With ListBox1
   
7.     'If Selection.Count > 7 Then
   
8.   '  .List = Selection.Value
   
9.   '  Else
   
10.     .Clear
    
11.    ' .AddItem Selection
    
12.    ' End If
    
13.    ' .ColumnCount = Selection.Columns.Count
    
14.     End With
    
15.    
    
16. End Sub

½Æ»s¥N½X

¦^´_ 4# afu9240

GBKEE

¦^´_ 9# afu9240

¬O³o¼Ë¶Ü?

1. Sub old() '¦~ÄÖ¦Û°Ê¥N¤J
   
2.     Dim x1, x2, i As Integer
   
3.     Cells(1, 5) = Now()
   
4.     'x1 = YearFrac(Now(), 0) '³o¸Ì¿ù»~
   
5.     'x1 = Application.WorksheetFunction.YearFrac(Now(), 0)
   
6.     X = 2
   
7.     Do
   
8.         Cells(X, "C") = "=AGE(RC[-1])"  'RC[-1] R1C1ªº®æ¦¡°Ñ·Óªk
   
9.         X = X + 1
   
10.     Loop Until Cells(X, "B") = ""
    
11. End Sub
    
12. '***'Module1(¤@¯ë¼Ò²Õ)ªºµ{¦¡½X
    
13. '**¦Û­q¨ç¼Æ
    
14. Function AGE(D1)  
    
15.     AGE = "#NA"
    
16.     Application.Volatile (False)
    
17.     If IsDate(D1) And D1 > 0 Then AGE = DateDiff("YYYY", D1, Date)
    
18. End Function

½Æ»s¥N½X

GBKEE

¦^´_ 9# afu9240

1. Function AGE(D1 As Range)         'Module1(¤@¯ë¼Ò²Õ)
   
2.     AGE = "#NA"
   
3.     Application.Volatile (False)
   
4.     If IsDate(D1) And D1 > 0 Then
   
5.         AGE = DateDiff("m", D1, Date)
   
6.         AGE = Round(Int(AGE / 12), 0) & "." & AGE - (Round(Int(AGE / 12), 0) * 12)  ' ³Ñ¾l¤ë¥÷ªº¤p¼ÆÂI
   
7.         'AGE = Round((AGE / 12), 2)   '10¶i¦ìªº¤p¼ÆÂI
   
8.    End If
   
9. End Function

½Æ»s¥N½X

GBKEE

¦^´_ 11# afu9240

1. '** Val(¤å¦r) >  Âà´«¼Æ¦r******************
   
2.             AGE = Val(Round(Int(AGE / 12), 0) & "." & AGE - (Round(Int(AGE / 12), 0) * 12))  ' ³Ñ¾l¤ë¥÷ªº¤p¼ÆÂI
   
3.             
   
4.             '**10¶i¦ìªº¤p¼ÆÂI***¥­§¡¦~ÄÖ¸û¬°·Ç½T  *****************
   
5.             AGE = Round((AGE / 12), 2)   '10¶i¦ìªº¤p¼ÆÂI
   
6.             '*******************************************

½Æ»s¥N½X

1. Sub ¥­§¡¦~ÄÖ()
   
2.     Dim aa As Range
   
3.     Cells(1, 8) = Now()
   
4.     'Times = Cells(65536, x + 1).End(xlUp).Row
   
5.     Set aa = Range("d2:d" & [d2].End(xlDown).Row) '©Ò¦³¥[Á`½d³ò
   
6.    
   
7.     Sheets("¤u§@ªí1").Cells(9, 9) = Application.WorksheetFunction.Sum(aa) / aa.Count
   
8. End Sub

½Æ»s¥N½X

GBKEE

¦^´_ 13# afu9240
µ¹ªºµ{¦¡½X½Ð¦h¤F¸Ñ¤~·|¬°¤v¥Î

1. '** Val(¤å¦r) >  Âà´«¼Æ¦r****¬O¦]¬°funtion¶Ç¦^¤å¦r ,SUM()µLªk¥[Á`,¦Ó­×§ïªº
   
2.             AGE = Val(Round(Int(AGE / 12), 0) & "." & AGE - (Round(Int(AGE / 12), 0) * 12))  ' ³Ñ¾l¤ë¥÷ªº¤p¼ÆÂI
   
3. 
   
4.             '**10¶i¦ìªº¤p¼ÆÂI***¥­§¡¦~ÄÖ¸û¬°·Ç½T  *****************
   
5.             AGE = Round((AGE / 12), 2)   '10¶i¦ìªº¤p¼ÆÂI    'funtion¶Ç¦^¼Æ¦r ,SUM()¥i¥H¥[Á`¤£¥²­×§ï
   
6.             '******************************************

½Æ»s¥N½X

£¿¦¹µ{¦¡½X¦³µù¸Ñ,½Ð¦A¤F¸Ñ¬Ý¬Ý ,¤G¿ï¤@¨Ï¥Î

GBKEE

¦^´_ 16# afu9240

1. Function AGE(D1 As Date)     'Module1(¤@¯ë¼Ò²Õ)
   
2.         Application.Volatile (False)
   
3.         If IsDate(D1) And D1 > 0 Then
   
4.             AGE = DateDiff("m", D1, Date)
   
5.             'AGE = Round(Int(AGE / 12), 0) & "." & AGE - (Round(Int(AGE / 12), 0) * 12)  ' ¤p¼ÆÂI¬°³Ñ¾l¤ë¥÷
   
6.             '***¦¹AGEªºµ{¦¡½X¤¤**  & "." & ** ¶Ç¦^¤å¦r¤£¯à­pºâ,¥ÎVAL()¨ç¼Æ±N¤å¦rÂର¼Æ¦r,¥i­pºâ
   
7.             AGE = Val(Round(Int(AGE / 12), 0) & "." & AGE - (Round(Int(AGE / 12), 0) * 12))  ' ¤p¼ÆÂI¬°³Ñ¾l¤ë¥÷
   
8.             
   
9.             'AGE = Round((AGE / 12), 2)   '¤p¼ÆÂI¬°10¶i¦ì  '***¦¹AGE¶Ç¦^¼Æ¦r,¥i­pºâ
   
10.             
    
11.        End If
    
12. End Function

½Æ»s¥N½X

1. Private Sub CommandButton1_Click()
   
2.     With Worksheets("¤H­û¦~¸ê¤ÀªRªí")
   
3.         If .AutoFilterMode Then .UsedRange.AutoFilter
   
4.     End With
   
5.     End
   
6. End Sub
   
7. 'Private Sub CommandButton4_Click() '*****
   
8. Private Sub ComboBox4_Change()  '¥i§ï¥ÎChange¤£¶·¦A«ö¬d¸ß
   
9.     Dim Rng As Range, AGE_Average As Double
   
10.     'If ComboBox4.ListIndex = -1 Then MsgBox "½Ð¿é¤J¥¿½Tªº­È": Exit Sub
    
11.     If ComboBox4.ListIndex = -1 Then Exit Sub        '.ListIndex = -1 ¤£¦b²M³æªº¤º®e
    
12.     Set Rng = Worksheets("¤H­û¦~¸ê¤ÀªRªí").Range("A2")
    
13.     If Rng.Parent.AutoFilterMode Then Rng.AutoFilter   '¨ú®ø¦Û°Ê¿z¿ï
    
14.     Set Rng = Range(Rng, Rng.End(xlToRight).End(xlDown))
    
15.     Rng.AutoFilter 1, ComboBox4.Value
    
16.     Set Rng = Range(Rng.Cells(2, 1), Rng.End(xlToRight).End(xlDown)).SpecialCells(xlCellTypeVisible)
    
17.     AGE_Average = Round(Application.WorksheetFunction.Average(Rng.Columns("G")), 2)
    
18.     MsgBox ComboBox4 & " ³¡ªù" & vbLf & "¥­§¡¦~ÄÖ " & AGE_Average
    
19.    
    
20.     With Worksheets("¤u§@ªí1")
    
21.         .Cells.Clear
    
22.         Rng.Copy .[a1]
    
23.         With .Range("g1", .Range("g1").End(xlDown).Address)
    
24.             .Cells(.Count + 1) = "=Average(" & .Cells.Address & ")"
    
25.            .Cells(.Count + 1).NumberFormatLocal = "0.00_)"
    
26.         End With
    
27.     End With
    
28. End Sub

½Æ»s¥N½X