ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

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

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

¦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

LeoV66.rar (10.87 KB)

¦^´_ 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

TOP

¦^´_ 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!!

TOP

¦^´_ 3# myleoyes
  1. If ActiveCell.Comment.Text Like "*°tªÑ*" Then ActiveCell.Font.ColorIndex = 5
½Æ»s¥N½X

TOP

¦^´_ 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!!

TOP

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

TOP

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

LeoV67.rar (8.69 KB)

TOP

¦^´_ 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]

TOP

¦^´_ 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. Sub Ex()
  6.       Selection = CDate(Application.Ceiling((DateSerial(Year(Date), 1, 1) - 7) / 7, 1) * 7 + 7 * 3)
  7.       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)))"
  8. End Sub
½Æ»s¥N½X

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

TOP

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

TOP

        ÀR«ä¦Û¦b : ±o²z­nÄǤH¡A²zª½­n®ð©M¡C
ªð¦^¦Cªí ¤W¤@¥DÃD