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

½Ð°Ý¦p¦óÅý¨ä¥LÄæ¦ì¨Ì·Ó¤µ¤Ñ¤é´ÁÅã¥Ü¤£¦P©³¦â

½Ð°Ý¦p¦óÅý¨ä¥LÄæ¦ì¨Ì·Ó¤µ¤Ñ¤é´ÁÅã¥Ü¤£¦P©³¦â

¥»©«³Ì«á¥Ñ coafort ©ó 2022-9-21 14:34 ½s¿è

½Ð°Ý¦U¦ì¤j¤j
§ÚAO³o­ÓÄæ¦ì¬O¤é´Á
BA³o­ÓÄæ¦ì¬O¼Æ­È
§Ú·QÅý¤µ¤Ñ¤é´ÁªºBA©³¦â¤£¦P
¤ñ¤è
AO3¬O2022/9/20 BA3Äæ¦ì©³¦â¥¿±`
AO4¬O2022/9/21 BA4Äæ¦ì©³¦âÅܦ¨µµ¦â
½Ð°Ý¸Ó¦p¦ó³]­p¡H
¥t¥~½Ð±Ð
¦p¦ó³]­p¥i¥H¥uÁ`­p¤µ¤éBA¼Æ­È
¤ñ¤è
AO3¬O2022/9/20 BA3¼Æ­È4
AO4¬O2022/9/21 BA4¼Æ­È5
AO5¬O2022/9/21 BA5¼Æ­È6
µM«áAB2Åã¥ÜBA4+BA5
ÁÂÁÂ

¦^´_ 1# coafort


    ÁÂÁ«e½úµoªí¦¹¥DÃD
VBA¤èªk¨Ñ°Ñ¦Ò
«á¾Ç½m²ß3ºØ¤èªk
  1. Option Explicit
  2. Sub TEST_1()
  3. 'Åý¤µ¤Ñ¤é´ÁªºBA©³¦â¤£¦P
  4. '¥uÁ`­pAOÄæ¬O¤µ¤éªºBA¼Æ­È¦bBA2
  5. Application.ScreenUpdating = False
  6. Dim i
  7. [BA2] = ""
  8. For i = 3 To Cells(Rows.Count, "AO").End(xlUp).Row
  9.    If Cells(i, "AO") = Date Then
  10.       Cells(i, "AO").Interior.ColorIndex = 17
  11.       [BA2] = [BA2] + Cells(i, "BA")
  12.       Else
  13.          Cells(i, "AO").Interior.ColorIndex = xlNone
  14.    End If
  15. Next
  16. End Sub
  17. Sub TEST_2()
  18. 'Åý¤µ¤Ñ¤é´ÁªºBA©³¦â¤£¦P
  19. '¥uÁ`­pAOÄæ¬O¤µ¤éªºBA¼Æ­È¦bBA2
  20. Application.ScreenUpdating = False
  21. Dim i As Range, Y
  22. Set Y = CreateObject("Scripting.Dictionary")
  23. [BA2] = ""
  24. For Each i In Range([AO3], Cells(Rows.Count, "AO").End(3))
  25.    Y.Add i, i.Item(1, 13)
  26.    If i = Date Then
  27.       i.Interior.ColorIndex = 17
  28.       [BA2] = [BA2] + Y(i)
  29.       Else
  30.          i.Interior.ColorIndex = xlNone
  31.    End If
  32. Next
  33. End Sub
  34. Sub TEST_3()
  35. 'Åý¤µ¤Ñ¤é´ÁªºBA©³¦â¤£¦P
  36. '¥uÁ`­pAOÄæ¬O¤µ¤éªºBA¼Æ­È¦bBA2
  37. Application.ScreenUpdating = False
  38. Dim R&, Y, Z As Range, xR As Range
  39. Set Y = CreateObject("Scripting.Dictionary")
  40. [BA2] = ""
  41. Set Z = Range([AO3], Cells(Rows.Count, "AO").End(3))
  42. Z.Interior.ColorIndex = xlNone
  43. For Each xR In Z
  44.    Y(xR.Value) = Y(xR.Value) + xR.Item(1, 13).Value
  45.    If xR = Date Then
  46.       xR.Interior.ColorIndex = 17
  47.    End If
  48. Next
  49. [BA2] = Y(Date)
  50. End Sub
½Æ»s¥N½X

TOP

¦^´_  coafort


    ÁÂÁ«e½úµoªí¦¹¥DÃD
VBA¤èªk¨Ñ°Ñ¦Ò
«á¾Ç½m²ß3ºØ¤èªk
Andy2483 µoªí©ó 2022-9-21 16:48



ÁÂÁ¤j¤j
¦ý¬O§Ú¦³¤W¦Ê¦C
½Ð°Ý³oµ{¦¡¬O§_¯à¥Î¡H
ÁÂÁÂ

TOP

¦^´_ 3# coafort
        ÁÂÁ«e½ú¦^ÂÐ
¤W¦Ê¦C¬O¥i¥Hªº
½Ð«e½ú ¸Õ¬Ý¬Ý

TOP

¦^´_  coafort
        ÁÂÁ«e½ú¦^ÂÐ
¤W¦Ê¦C¬O¥i¥Hªº
½Ð«e½ú ¸Õ¬Ý¬Ý
Andy2483 µoªí©ó 2022-9-21 19:00


½Ð°Ý¤W¦Ê¦C»Ý­n§ï¤°»ò©O¡H
ÁÂÁ¤j¤j

TOP

¦^´_ 5# coafort


    ÁÂÁ«e½ú¦^ÂÐ
²©ö»s§@¼ÒÀÀ±¡¹Ò½d¨Ò,½Ð«e½ú´ú¸Õ¬Ý¬Ý


TEST_20220922.zip (44.86 KB)
  1. Sub ¶Ã¼Æ§G°}()
  2. Dim Brr(1 To 1000, 1 To 13), i, E
  3. Cells.Interior.ColorIndex = xlNone
  4. Call ²M°£¿z¿ï

  5. Brr(1, 1) = "¤é´Á"
  6. Brr(1, 13) = "¤µ¤é¦X­p"
  7. For i = 2 To UBound(Brr)
  8.    Brr(i, 1) = "=TODAY() + " & Int(Rnd() * 100) Mod 5
  9.    Brr(i, 13) = Int(Rnd() * 100)
  10. Next
  11. With [AO2].Resize(UBound(Brr), UBound(Brr, 2))
  12.    .Value = Brr
  13.    .Value = .Value
  14. End With
  15. End Sub
  16. Sub ¿z¿ï¤µ¤Ñ()
  17. Call ²M°£¿z¿ï

  18. If Application.Version >= 12 Then
  19.    Selection.AutoFilter Field:=41, Criteria1:= _
  20.         xlFilterToday, Operator:=xlFilterDynamic
  21.    Else
  22.       uDATE = Date
  23.       Selection.AutoFilter Field:=41, Criteria1:=uDATE, Operator:=xlAnd
  24. End If
  25. End Sub
  26. Sub ²M°£¿z¿ï()
  27. If Sheets(1).AutoFilter Is Nothing Then
  28.    [A2:BA2].AutoFilter
  29.    Else
  30.    If Sheets(1).FilterMode = True Then Sheets(1).ShowAllData
  31. End If
  32. End Sub
½Æ»s¥N½X

TOP

¦^´_  coafort


    ÁÂÁ«e½ú¦^ÂÐ
²©ö»s§@¼ÒÀÀ±¡¹Ò½d¨Ò,½Ð«e½ú´ú¸Õ¬Ý¬Ý
Andy2483 µoªí©ó 2022-9-22 08:37


½Ð°Ý¤j¤j
¥i¥H³]­p¦¨¤£¥Î«ö¿z¿ï
µM«á·|¦Û¤v¿z¿ï¶Ü
ÁÂÁ¤j¤j

TOP

¦^´_ 6# Andy2483

ÁÙ¬O»¡³]©w®æ¦¡¤Æ±ø¥ó
¦³¿ìªk³]©w¤½¦¡
Åý¤µ¤Ñ¤é´Áªº«ü©wÄæ¦ìÅܦâ¡H


ÁÂÁ¤j¤j

TOP

google"EXCEL°g"  blog  ©Îgoogleºô§}:https://hcm19522.blogspot.com/

TOP

¥»©«³Ì«á¥Ñ Andy2483 ©ó 2022-9-22 09:57 ½s¿è

¦^´_ 8# coafort


    ÁÂÁ«e½ú¦^ÂÐ
1.«á½ú¹ï®æ¦¡¤Æ±ø¥ó¤£¼ô,¤u§@ªÅÀÉ¥¿¿n·¥¾Ç²ßVBA
2.«á¾Ç¹B¥ÎIJµo¤è¦¡½d¨Ò¦p¤U
TEST_20220922_1.zip (45.8 KB)

  1. Private Sub Worksheet_Change(ByVal Target As Range)
  2. With Target
  3.    If .Column = [AO3].Column And .Count = 1 And .Row >= 3 Then
  4.       Call TEST_1
  5.       
  6.    End If
  7. End With
  8. End Sub
½Æ»s¥N½X

TOP

        ÀR«ä¦Û¦b : ¤â¤ß¦V¤U¬O§U¤H¡A¤â¤ß¦V¤W¬O¨D¤H¡F§U¤H§Ö¼Ö¡A¨D¤Hµh­W¡C
ªð¦^¦Cªí ¤W¤@¥DÃD