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

¥ÎVBA°µ¬d¸ß¨t²Î

¦^´_ 9# aassddff736

©êºp! §Ñ¤F»¡ §Ú·Q­n¤u§@ªí­ì®æ¦¡¦b¥D­¶¤W¥i¥H¶Ü? ¨º¨ÇÀx¦s®æ¦â±m¬O¼Ð°O¥Îªº

TOP

¦^´_ 9# aassddff736

1.¦pªG¿ûªO¨S¦³«Ø¥ßÀx¦ì½s¸¹ BÄæ¯d¥Õ,¦ý¬O³ø¼o»P±a³ø¼o¿ûªOBÄæÅã¥Ü
If R > E  Then Crr(R, 1) = S
§ï¬°
If R > E And InStr("«Ý³ø¼o", S) Then Crr(R, 1) = S

2.«ç»ò¬dªÅÀx¦ì?
¤â°Ê ¿z¿ïDÄæ > ªÅ®æ
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¦^´_ 12# Andy2483


    ¤]¬Oª½±µ¿z¿ï¤ñ¸û§Ö

TOP

¦^´_ 11# aassddff736

¦pªG¥þ³¡®æ¦¡³£­n¹L¥h,½Ð¥ý¦Û¤v¸ÕµÛ¿ý»s¥¨¶°,±µÀs¨ì¥D­¶ªí
µo¸ÜÃDªº½d¨ÒÀ³¸Ó­n§t®æ¦¡³£½Æ»s¹L¥hÅý¨ó§UªÌ©ú¥Õ»Ý¨D
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¦^´_  Andy2483


    ¤]¬Oª½±µ¿z¿ï¤ñ¸û§Ö
aassddff736 µoªí©ó 2024-3-7 14:22



    ¸ê®Æ¦pªG¦³¶×¨ì¥D­¶,¿z¿ïªº°Ê§@¥N½X¿ý»s¥¨¶°´N¥i¥H¿ì¨ì
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¦^´_ 15# Andy2483


    ·PÁ±z §Ú¦A¸Õ¸Õ¬Ý

TOP

¦^´_ 11# aassddff736
ÁÂÁ½׾Â,ÁÂÁ¦U¦ì«e½ú


Option Explicit
Sub ¸ê®Æ·J¾ã¤J¥D­¶¿z¿ï°Ï()
Application.ScreenUpdating = False: Application.DisplayAlerts = False
Dim Arr, Brr, Crr(1 To 10000, 1 To 1), Z, Q, i&, j%, R&, N&, S, T$, E&, TT$, xR As Range
Set Z = CreateObject("Scripting.Dictionary")
With Sheets("¥D­¶")
   .Activate: If .AutoFilter Is Nothing Then [B17:Q17].AutoFilter Else If .FilterMode = True Then .ShowAllData
   With ActiveWindow: .FreezePanes = False: .ScrollRow = 13: .SplitRow = 5: .FreezePanes = True: End With: .UsedRange.Offset(17).EntireRow.Delete
End With
Q = Array("1¦Ü588", "SUPER", "POWER", "POWER¸Õ²£", "TEST", "«Ý³ø¼o", "³ø¼o")
For Each S In Q
   Set xR = [D65536].End(3)(2, 0): If Sheets(S).FilterMode = True Then Sheets(S).ShowAllData
   R = Sheets(S).[B65536].End(3).Row - 2: Sheets(S).[A3].Resize(R, 14).Copy xR: If InStr("«Ý³ø¼o", S) Then xR.Resize(R, 1).Offset(, -1) = S
Next
Set Brr = Range([P18], [D65536].End(3)(1, -1)): Brr.Font.Size = 8: N = Brr.Rows.Count: Brr = Brr.Resize(10000).Resize(, 2)
For i = 1 To UBound(Brr): Z(Brr(i, 2)) = i: Next: Z.Remove ("")
Q = Array(Range([Àx¦ì!B3], [Àx¦ì!A65536].End(xlUp)), Range([Àx¦ì!E3], [Àx¦ì!D65536].End(xlUp)))
For Each Arr In Q
   Arr = Arr
   For i = 1 To UBound(Arr)
      T = Arr(i, 2): If Z.Exists(T) Then Brr(Z(T), 1) = Arr(i, 1) Else N = N + 1: Brr(N, 1) = Arr(i, 1): Brr(N, 2) = Arr(i, 2)
   Next
Next
With [B18].Resize(N, 2): .Value = Brr: .Resize(, 15).Borders.LineStyle = 1: .EntireRow.AutoFit: End With: Call µù¸Ñ_½Õ¾ã¦Ü«ü©w¦ì¸m
End Sub
Sub µù¸Ñ_½Õ¾ã¦Ü«ü©w¦ì¸m()
Dim CO As Comment, SL&, ST&
For Each CO In ActiveSheet.Comments
   With CO
      With Range(.Parent.Address): SL = .Left + .Width + 10: ST = .Top + 10: End With: With .Shape: .Left = SL: .Top = ST: End With
     .Shape.TextFrame.Characters.Font.Size = 12: .Shape.DrawingObject.AutoSize = True
   End With
Next
Application.DisplayCommentIndicator = -1
End Sub
Sub ²M°£¥D­¶¿z¿ï°Ï¸ê®Æ()
With Sheets("¥D­¶")
   .Activate: If .AutoFilter Is Nothing Then [B17:Q17].AutoFilter Else If .FilterMode = True Then .ShowAllData
   With ActiveWindow: .FreezePanes = False: .ScrollRow = 1: .SplitRow = 17: .FreezePanes = True: End With: .UsedRange.Offset(17).EntireRow.Delete
End With
End Sub
Sub ¥D­¶¿z¿ï°Ï¸ê®Æ_¥þ³¡Åã¥Ü()
With Sheets("¥D­¶")
   .Activate: If .AutoFilter Is Nothing Then [B17:Q17].AutoFilter Else If .FilterMode = True Then .ShowAllData
   With ActiveWindow: .FreezePanes = False: .ScrollRow = 13: .SplitRow = 5: .FreezePanes = True: .ScrollRow = 1: End With
   If .[D65536].End(3).Row = 17 Then Call ¸ê®Æ·J¾ã¤J¥D­¶¿z¿ï°Ï
End With
End Sub
Sub ªÅÀx¦ì()
Call ¥D­¶¿z¿ï°Ï¸ê®Æ_¥þ³¡Åã¥Ü: Selection.AutoFilter Field:=2, Criteria1:="<>": Selection.AutoFilter Field:=3, Criteria1:="="
End Sub
Sub ¨S¦³Àx¦ìªº¿ûªO()
Call ¥D­¶¿z¿ï°Ï¸ê®Æ_¥þ³¡Åã¥Ü: Selection.AutoFilter Field:=3, Criteria1:="<>": Selection.AutoFilter Field:=2, Criteria1:="="
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¦^´_ 17# Andy2483
«z!¤Ó§ó¿³¤F ÁÂÁ±z«Ü¦h §Ú¸Õ¸Õ

TOP

¦^´_ 17# Andy2483




½Ð°Ý¤@¤U§ÚÀx¦s®æA18¦V¤U±a¤J¶W³sµ²Àx¦s¤½¦¡vba¦p¦ó¼g
=IF(D18="","",HYPERLINK("#"&LOOKUP(1,0/COUNTIF(INDIRECT({"POWER¸Õ²£";"«Ý³ø¼o";"³ø¼o";"1¦Ü588";"SUPER";"POWER"}&"!B:B"),D18),{"POWER¸Õ²£";"«Ý³ø¼o";"³ø¼o";"1¦Ü588";"SUPER";"POWER"})&"!"&"B"&MATCH(D18,INDIRECT(LOOKUP(1,0/COUNTIF(INDIRECT({"POWER¸Õ²£";"«Ý³ø¼o";"³ø¼o";"1¦Ü588";"SUPER";"POWER"}&"!B:B"),D18),{"POWER¸Õ²£";"«Ý³ø¼o";"³ø¼o";"1¦Ü588";"SUPER";"POWER"})&"!"&"B:B"),0),"¡´"))

TOP

¦^´_ 19# aassddff736

Sub ¸ê®Æ·J¾ã¤J¥D­¶¿z¿ï°Ï()
Application.ScreenUpdating = False: Application.DisplayAlerts = False
Dim Arr, Brr, Crr(1 To 10000, 1 To 1), Z, Q, i&, j%, R&, N&, S, T$, E&, TT$, xR As Range
Set Z = CreateObject("Scripting.Dictionary")
With Sheets("¥D­¶")
   .Activate: If .AutoFilter Is Nothing Then [B17:Q17].AutoFilter Else If .FilterMode = True Then .ShowAllData
   With ActiveWindow: .FreezePanes = False: .ScrollRow = 13: .SplitRow = 5: .FreezePanes = True: End With: .UsedRange.Offset(17).EntireRow.Delete
End With
Q = Array("1¦Ü588", "SUPER", "POWER", "POWER¸Õ²£", "TEST", "«Ý³ø¼o", "³ø¼o")
For Each S In Q
   Set xR = [D65536].End(3)(2, 0): If Sheets(S).FilterMode = True Then Sheets(S).ShowAllData
   R = Sheets(S).[B65536].End(3).Row - 2: Sheets(S).[A3].Resize(R, 14).Copy xR: If InStr("«Ý³ø¼o", S) Then xR.Resize(R, 1).Offset(, -1) = S
   For i = 1 To xR.Resize(R, 1).Offset(, -2).Count
      ActiveSheet.Hyperlinks.Add Anchor:=xR.Resize(R, 1).Offset(, 1)(i), Address:="", SubAddress:=S & "!A" & i + 2 & ":O" & i + 2
   Next
Next
Set Brr = Range([P18], [D65536].End(3)(1, -1)): Brr.Font.Size = 8: Brr.Columns(3).Font.Size = 12: N = Brr.Rows.Count: Brr = Brr.Resize(10000).Resize(, 2)
For i = 1 To UBound(Brr): Z(Brr(i, 2)) = i: Next: Z.Remove ("")
Q = Array(Range([Àx¦ì!B3], [Àx¦ì!A65536].End(xlUp)), Range([Àx¦ì!E3], [Àx¦ì!D65536].End(xlUp)))
For Each Arr In Q
   Arr = Arr
   For i = 1 To UBound(Arr)
      T = Arr(i, 2): If Z.Exists(T) Then Brr(Z(T), 1) = Arr(i, 1) Else N = N + 1: Brr(N, 1) = Arr(i, 1): Brr(N, 2) = Arr(i, 2)
   Next
Next
With [B18].Resize(N, 2): .Value = Brr: .Resize(, 15).Borders.LineStyle = 1: .EntireRow.AutoFit: End With: Call µù¸Ñ_½Õ¾ã¦Ü«ü©w¦ì¸m
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

        ÀR«ä¦Û¦b : ºw¤ô¦¨ªe¡C²É¦Ì¦¨ÅÚ¡A¤Å»´¤vÆF¡A¤Å¥Hµ½¤p¦Ó¤£¬°¡C
ªð¦^¦Cªí ¤W¤@¥DÃD