µ{¦¡¦p¦ó¼g¤ñ¸û¶¶ºZ§Ö³t

myleoyes

¦U¦ì«e½ú§A­Ì¦n!
         «e½ú!!°ÝÃD¦pªþÀÉ»¡©ú
         ½Ðª¾¹Dªº«e½ú,¤£§[½ç±ÐÁÂÁ¦A¤T!!
Sub ½m²ß()
    With Sheets("½m²ß")
      [AG3] = "=AB2-SUM(AD2/2,AE2,AA3,AC3/2)": [AI3] = "=AG3/(AA3+AC3/2)"
      .Range("V" & Rows.Count).End(xlUp)(2, 1).Select: Selection = Date: ActiveCell.Offset(0, 1).Select
      ZZ = Application.InputBox("½Ð¿é¤J¼Æ¦r", "½m²ß¸ê®Æ", Type:=1)
      If ZZ <= 0 Then ActiveCell.Offset(0, -1) = "": [AG3,AI3] = "": End
      ActiveCell = ZZ: ActiveCell.Font.ColorIndex = 7: ActiveCell.Offset(0, 1).Select
ag:
      ZZ = Application.InputBox("½Ð¿é¤J¼Æ¦r", "¼Æ¾Ú¸ê®Æ", Type:=1)
      If ZZ <= 0 Then GoTo ag
      ActiveCell = ZZ: ActiveCell.Offset(0, 2).Select
      [Z3:AI3].Copy: ActiveSheet.Paste: Calculate: [AG3,AI3] = "": Application.CutCopyMode = False
      ActiveCell.Offset(, 2) = "": ActiveCell.Offset(, 4) = "": ActiveCell.Offset(, 5) = "": ActiveCell.Offset(, 8) = ""
      .Range("AC" & Rows.Count).End(xlUp).Select
      If Selection <= 150 Then Selection = 200: ActiveCell.Font.ColorIndex = 7
      .Range("AG" & Rows.Count).End(xlUp).Select: ActiveCell.Font.ColorIndex = 10
      .Range("AI" & Rows.Count).End(xlUp).Select
      If Selection < 0 Then ActiveCell.Font.ColorIndex = 3
: Calculate: .Columns("V:AI").EntireColumn.AutoFit
      End With
      ActiveWorkbook.Save
End Sub

GBKEE

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

1. Sub ½m²ß()
   
2.     Dim Rng As Range
   
3.     With Sheets("½m²ß")
   
4.         .[AG3] = "=AB2-SUM(AD2/2,AE2,AA3,AC3/2)": .[AI3] = "=AG3/(AA3+AC3/2)"
   
5.         Set Rng = Sheets("½m²ß").Range("V" & Rows.Count).End(xlUp)(2, 1)
   
6.         Rng = Date
   
7.         ZZ = Application.InputBox("½Ð¿é¤J¼Æ¦r", "½m²ß¸ê®Æ", Type:=1)
   
8.         If ZZ <= 0 Then Rng = "": .[AG3,AI3] = "": End
   
9.         With Rng.Cells(1, 2)    '¥ÑRngªºÄæ¦ìºâ°_²Ä2Äæ¦P¤@¦CªºÀx¦s®æ = WÄæ
   
10.             .Value = ZZ
    
11.             .Font.ColorIndex = 7
    
12.         End With
    
13. ag:
    
14.         ZZ = Application.InputBox("½Ð¿é¤J¼Æ¦r", "¼Æ¾Ú¸ê®Æ", Type:=1)
    
15.         If ZZ <= 0 Then GoTo ag
    
16.         With Rng.Cells(1, 3)   '¥ÑRngªºÄæ¦ìºâ°_²Ä3Äæ¦P¤@¦CªºÀx¦s®æ=XÄæ
    
17.             .Value = ZZ
    
18.             .Font.ColorIndex = 7
    
19.         End With
    
20.         With Rng.Cells(1, 5)    '¥ÑRngªºÄæ¦ìºâ°_²Ä4Äæ¦P¤@¦CªºÀx¦s®æ=ZÄæ
    
21.             For Each e In Array(1, 2, 4, 8, 10) '°}¦C: ¥ÑZÄæºâ°_ªºÄæ
    
22.                 With .Cells(1, e)               '¥ÑZÄæºâ°_ªºÄæ¦P¤@¦CªºÀx¦s®æ
    
23.                     .FormulaR1C1 = Cells(3, .Column).FormulaR1C1
    
24.                     If e = 4 And .Value <= 150 Then
    
25.                         .Value = 200
    
26.                         .Font.ColorIndex = 7
    
27.                     ElseIf e = 8 Then
    
28.                         .Font.ColorIndex = 10
    
29.                     ElseIf e = 10 And .Value < 0 Then
    
30.                         .Font.ColorIndex = 3
    
31.                     End If
    
32.                 End With
    
33.             Next
    
34.         End With
    
35.         .Columns("V:AI").EntireColumn.AutoFit
    
36.         '¦p¬¡­¶Ã¯³]©w­pºâ :¤è¦¡¬°¤â°Ê,¨º¤~¶·­«ºâ¦¹¤u§@ªí.
    
37.        .Calculate  '­«ºâ¦¹¤u§@ªí.
    
38.        '¦¹­«ºâ ¤]³\¬O§A»{¬°°õ¦æ°_¨Óı±o«ÜºC ªº¥D¦]
    
39.      End With
    
40.      ActiveWorkbook.Save
    
41. End Sub

½Æ»s¥N½X

myleoyes

¦^´_ 2# GBKEE
¨}®v!ÁÂÁÂ!µ{¦¡OK! ¤U­Ó½d¨Ò¦A³Â·Ð¤@¤U
      ·íÀx¦s®æ¸Ñµù¤º®eªº³Ì«á¨â­Ó¦r¬O°tªÑ
        µ{¦¡´N·|§ïÅÜÀx¦s®æªºÃC¦â
        ¨Ò¦pD3¸Ñµù=Ápµú°tªÑÅÜÃC¦â
                D4¸Ñµù=Ápµú°t®§¤£·|ÅÜÃC¦â
                D5¸Ñµù=¥x¿n¹q°tªÑ¤]ÅÜÃC¦â
        ¦p¦ó­×§ï¦¹µ{¦¡¤~¯à¦X¥G»Ý¨D
Sub ªÑ§Q©Ò±o()
Again:
    ZZ = Application.InputBox("¿é¤Jª÷ÃB", "½Ð¿é¤JªÑ§Q¦¬¤J", "10,000", Type:=1)
    [A15] = ZZ
    If ZZ > 10000000 Then [A15] = 10000000
    If [A15] = False Then [A15] = "": End
    If ZZ <= 5000 Then MsgBox "ªÑ§Q¦¬¤J¤Ö©ó©Îµ¥©ó5000¤£¦©¸É¥R«O¶O¡I¡I": [A15] = "": GoTo Again
    [A15] = [A14] * [A15]
    With Sheets("À³¦©ÃB")
      .[A15].Copy .[D100].End(xlUp)(2, 1)
      .[D15:D100].Font.ColorIndex = 7
      .Range("D" & Rows.Count).End(xlUp).Select
      If ActiveCell.Comment Is Nothing Then ActiveCell.AddComment
       ZZ = InputBox("½Ð¿é¤JªÑ²¼¦WºÙ»P°t®§©Î°tªÑ", "¸Ñµù", " °t®§ ©Î°tªÑ", 0)
    If ZZ = "" Then ActiveCell.Comment.Delete: Exit Sub
       ActiveCell.Comment.Text Text:=ZZ
      If ActiveCell.Comment.Text = "°tªÑ" Then ActiveCell.Font.ColorIndex = 5
      [A15] = "": [D15].Select
    End With
End Sub
     ¨}®v!½Ð¦A¨¯­WÅo!ÁÂÁ¦A¤T!!

GBKEE

¦^´_ 3# myleoyes

1. If ActiveCell.Comment.Text Like "*°tªÑ*" Then ActiveCell.Font.ColorIndex = 5

½Æ»s¥N½X

myleoyes

¦^´_ 4# GBKEE
¨}®v!ÁÂÁÂ!µ{¦¡OK!¤£¦n·N«ä³o­Ó½d¨Ò¦A³Â·Ð¤@¤U
Sub Ex()
            Selection = CDate(Application.Ceiling((DateSerial(Year(Date), 1, 1) - 7) / 7, 1) * 7 + 7
* 3)
            Selection.Offset(0, 1) = "=IF(ISNUMBER(MATCH(A1,EDATE(A22,{0,6,12})-(WEEKDAY(EDATE(A22,
{0,6,12}),2)-6),0)),""ºû­×""&LOOKUP(A1,EDATE(A22,{0,6,12})-(WEEKDAY(EDATE(A22,{0,6,12}),2)-6),
{1,2,0})&""¦¸"",INDEX(EDATE(A22,{12,6,0})-(WEEKDAY(EDATE(A22,{12,6,0}),2)-6),MATCH(A1,EDATE(A22,
{12,6,0})-(WEEKDAY(EDATE(A22,{12,6,0}),2)-6),-1)))"
           End Sub
        ·íÂI¿ïA22®É,µ{¦¡¦bB22¼g¤J=IF(ISNUMBER(MATCH(A1,EDATE(A22,{0,6,12})-....,-1)))¤½¦¡
        ¦ý°ÝÃD¬O·íÂI¿ïA25®É,µ{¦¡¦bB25¼g¤JªºÁÙ¬O"=IF(ISNUMBER(MATCH(A1,EDATE(A22,{0,6,12})-....,-
1)))"  ¤½¦¡
        ¤]´N¬O»¡ A22 ¨ÃµLªkÅܦ¨ A25
        ¤£ª¾¦³¤è¦¡¥i¥H¸Ñ¨M¦¹ÃøÃD
        ½Ð¤£§[½ç±ÐÁÂÁ¦A¤T!!

GBKEE

¦^´_ 5# myleoyes
¥ÎR1C1 ªí¥Üªk
"=IF(ISNUMBER(MATCH(A1,EDATE(RC[-1],{0,6,12})-....,-"

myleoyes

¦^´_ 6# GBKEE
¨}®v!ÁÂÁÂ!¬°¦ó?«ö¶s·|¬O¿ù»~°T®§©O?   
       =IF(ISNUMBER(MATCH('A1',EDATE(A24,{0,6,12})...
       A1¬°¦óÅܦ¨'A1'
      ¦pªþÀÉ©Ò¥Ü
     ½Ð¦A³Â·Ð«ü¾ÉÁÂÁ¦A¤T!!¨¯­WÅo!

GBKEE

¦^´_ 7# myleoyes

¥ÎR1C1 ªí¥Üªk"=IF(ISNUMBER(MATCH(A1,EDATE(RC[-1],{0,6,12})-....,-"

A1 ¨S§ï¨ì
"=IF(ISNUMBER(MATCH(R1C1,EDATE(RC[-1],{0,6,12})-....,-"[/quote]

c_c_lai

¦^´_ 7# myleoyes
¦^´_ 8# GBKEE
Sub ºû­×()»PSub Ex() ¥¿¦p GBKEE ¤j¤j¤§­×¥¿«áªºµ²ªG¡G

1. Sub ºû­×()
   
2.       Selection = CDate(Application.Ceiling((DateSerial(Year(Date), 1, 1) - 7) / 7, 1) * 7 + 7 * 3)
   
3.       Selection.Offset(0, 1) = "=IF(ISNUMBER(MATCH(R1C1,EDATE(RC[-1],{0,6,12})-(WEEKDAY(EDATE(RC[-1],{0,6,12}),2)-6),0)),""ºû­×""&LOOKUP(R1C1,EDATE(RC[-1],{0,6,12})-(WEEKDAY(EDATE(RC[-1],{0,6,12}),2)-6),{1,2,0})&""¦¸"",INDEX(EDATE(RC[-1],{12,6,0})-(WEEKDAY(EDATE(RC[-1],{12,6,0}),2)-6),MATCH(R1C1,EDATE(RC[-1],{12,6,0})-(WEEKDAY(EDATE(RC[-1],{12,6,0}),2)-6),-1)))"
   
4. End Sub
   
5. 
   
6. Sub Ex()
   
7.       Selection = CDate(Application.Ceiling((DateSerial(Year(Date), 1, 1) - 7) / 7, 1) * 7 + 7 * 3)
   
8.       Selection.Offset(0, 1) = "=IF(ISNUMBER(MATCH(A1,EDATE(A22,{0,6,12})-(WEEKDAY(EDATE(A22,{0,6,12}),2)-6),0)),""ºû­×""&LOOKUP(A1,EDATE(A22,{0,6,12})-(WEEKDAY(EDATE(A22,{0,6,12}),2)-6),{1,2,0})&""¦¸"",INDEX(EDATE(A22,{12,6,0})-(WEEKDAY(EDATE(A22,{12,6,0}),2)-6),MATCH(A1,EDATE(A22,{12,6,0})-(WEEKDAY(EDATE(A22,{12,6,0}),2)-6),-1)))"
   
9. End Sub

½Æ»s¥N½X

¤U¸ü (6.39 KB)

2012-10-28 07:41

GBKEE ¤j¤j¡A¨ì¥Ø«e¬°¤î¡A§Ú¤]ÁA¸Ñ¤F FormulaR1C1 ªºÀ³¥Î¡AÁÂÁ±z¡I

c_c_lai

¦^´_ 8# GBKEE
½Ð±Ð±z¬°¤°»òSub ºû­×()»PSub Ex()°õ¦æ¥X¨Óªºµ²ªG­È¤£¤@¼Ë©O¡H
¥ªÃ䧹¥þ¬OSub Ex()°õ¦æ¥X¨Óªºµ²ªG¡A¥k¤è«h¬O¨âªÌ²V¦X¥æ¿ù°õ¦æ¡C

¤U¸ü (89.9 KB)

2012-10-28 08:12