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

[µo°Ý] ¦C¥X§ó¦hªº¹ïÀ³¸ê®Æ

¥»©«³Ì«á¥Ñ n7822123 ©ó 2020-8-22 23:16 ½s¿è

¦^´_ 29# °a¤ªºµ

§A¥i¥HÄ~Äò§V¤O~~

¦Û¤v¨¯­W¼g¥X¨Óªºµ{¦¡¡A·|«Ü¦³¦¨´N·P!

¾Ç²ß´N¬O¤£Â_¹Á¸Õ¿ù»~ªº¹Lµ{~

¬Û¤Ï¡A¦pªG¥u§Û§O¤Hªºµ{¦¡¡A´N»{¬°¦Û¤v¤w¸g·|ªº¨ººØ¤H....¬O¾Ç¤£¦nªº

¥H¬°¦Û¤v¤w¸g¬ÝÀ´¡A¦ý´N¬O¤£¯à¦Û¤v¼g¥X¨Ó~
µ{¦¡¬O¨Ì»Ý¨D¼gªº¡A»Ý¨Dªí¹F¤£²M·¡
©ÎªÌ¨S¦³¤W¶Çªþ¥ó¡A·R²ö¯à§U

TOP

¥»©«³Ì«á¥Ñ °a¤ªºµ ©ó 2020-8-23 04:05 ½s¿è

ÁÂÁ n7822123 «e½úªº¹ªÀy  ¨C¼g¤@¦¸§Ú´N·|¦h¤@¦¸¸gÅç §Ú·|Ä~Äò§V¤Oªº:P

¦^´_ 30# qaqa3296
   
©êºp ¨S¦³ª`·N¨ì §Ú§ï³o¼Ë §A¬Ý¬Ý¬O¤£¬O³o¼Ëªºµ²ªG

Public Sub ¼Ò½k¿z¿ï()
Application.ScreenUpdating = False
Range(Sheets(2).Cells(2, 1), Sheets(2).Cells(2, 4).End(xlDown)).Font.Color = RGB(255, 0, 0)
G = True
Sheets(3).Select
Sheets(3).Range(Cells(1, 1), Cells(1, 4).End(xlDown)).Clear
Sheets(2).Select

For K = 2 To Cells(2, 5).End(xlDown).Row
    x = Sheets(1).Cells(K, 3)
    If Sheets(1).Cells(K, 1) = "" Then Exit For
    For i = 2 To Cells(2, 3).End(xlDown).Row '¨Ì±ø¥ó¿z¿ï

        If Sheets(1).Cells(K, 3) = "" And Asc(Sheets(1).Cells(K, 2)) > 127 Or Asc(Sheets(1).Cells(K, 2)) < 0 Then
            Cells(i, 1).AutoFilter Field:=2, Criteria1:=Sheets(1).Cells(K, 2)
            Range(Sheets(2).Cells(2, 1), Sheets(2).Cells(2, 4).End(xlDown)).Font.Color = RGB(0, 0, 0)
            
            If Range(Sheets(2).Cells(2, 1), Sheets(2).Cells(2, 4).End(xlDown)).Count - 1 And Sheets(1).Cells(K, 3) = "" Then
                Sheets(2).Cells(2, 3).AutoFilter
                Cells(i, 1).AutoFilter Field:=1, Criteria1:=Sheets(1).Cells(K, 1)
                Range(Sheets(2).Cells(2, 1), Sheets(2).Cells(2, 4).End(xlDown)).Font.Color = RGB(0, 0, 0)
            End If

            
        Else
           Cells(i, 1).AutoFilter Field:=3, Criteria1:="*" & x & "*"
           Range(Sheets(2).Cells(2, 1), Sheets(2).Cells(2, 4).End(xlDown)).Font.Color = RGB(0, 0, 0)
        End If
        
        If G = True Then
           Range(Sheets(2).Cells(1, 1), Sheets(2).Cells(1, 4).End(xlDown)).Copy Sheets(3).Cells(1, 1)
           G = False
        Else
           Range(Sheets(2).Cells(2, 1), Sheets(2).Cells(2, 4).End(xlDown)).Copy Sheets(3).Cells(1, 1).End(xlDown).Offset(1, 0)
        End If
        Sheets(2).Cells(2, 3).AutoFilter
    Exit For
    Next i
Next K
Sheets(3).Select
Range(Sheets(3).Cells(2, 1), Sheets(3).Cells(2, 4).End(xlDown)).RemoveDuplicates Columns:=1
Range(Sheets(3).Cells(2, 1), Sheets(3).Cells(2, 4).End(xlDown)).Font.Color = RGB(0, 0, 0)
Application.ScreenUpdating = True
End Sub
javascript:;

¦C¥X§ó¦h¸ê®ÆV6001.rar (32.59 KB)

TOP

¦pªG¬O «~¦WÁÙ¦³³W®æ ¥´¿ù¦r §Ú¬O§ï³o¼Ë ¦ý¬O ³W®æªºµ²ªG¸ò ·Ç¤jªº¤£¦P   
Public Sub ¼Ò½k¿z¿ï()
Application.ScreenUpdating = False
Range(Sheets(2).Cells(2, 1), Sheets(2).Cells(2, 4).End(xlDown)).Font.Color = RGB(255, 0, 0)
G = True
Sheets(3).Select
Sheets(3).Range(Cells(1, 1), Cells(1, 4).End(xlDown)).Clear
Sheets(2).Select

For K = 2 To Cells(2, 5).End(xlDown).Row
    x = Sheets(1).Cells(K, 3)
    If Sheets(1).Cells(K, 1) = "" Then Exit For
    For i = 2 To Cells(2, 3).End(xlDown).Row '¨Ì±ø¥ó¿z¿ï

        If Sheets(1).Cells(K, 3) = "" And Asc(Sheets(1).Cells(K, 2)) > 127 Or Asc(Sheets(1).Cells(K, 2)) < 0 Then
            Cells(i, 1).AutoFilter Field:=2, Criteria1:=Sheets(1).Cells(K, 2)
            Range(Sheets(2).Cells(2, 1), Sheets(2).Cells(2, 4).End(xlDown)).Font.Color = RGB(0, 0, 0)
            
            If Range(Sheets(2).Cells(2, 1), Sheets(2).Cells(2, 4).End(xlDown)).Count - 1 And Sheets(1).Cells(K, 3) = "" Then
                Sheets(2).Cells(2, 3).AutoFilter
                Cells(i, 1).AutoFilter Field:=1, Criteria1:=Sheets(1).Cells(K, 1)
                Range(Sheets(2).Cells(2, 1), Sheets(2).Cells(2, 4).End(xlDown)).Font.Color = RGB(0, 0, 0)
                If Range(Sheets(2).Cells(2, 1), Sheets(2).Cells(2, 4).End(xlDown)).Count - 1 And Sheets(1).Cells(K, 3) <> "" Then
                    Sheets(2).Cells(2, 3).AutoFilter
                    Cells(i, 1).AutoFilter Field:=1, Criteria1:=Sheets(1).Cells(K, 1)
                    Range(Sheets(2).Cells(2, 1), Sheets(2).Cells(2, 4).End(xlDown)).Font.Color = RGB(0, 0, 0)
                End If
            End If
            
        Else
           Cells(i, 1).AutoFilter Field:=3, Criteria1:="*" & x & "*"
           Range(Sheets(2).Cells(2, 1), Sheets(2).Cells(2, 4).End(xlDown)).Font.Color = RGB(0, 0, 0)
        End If
        
        If G = True Then
           Range(Sheets(2).Cells(1, 1), Sheets(2).Cells(1, 4).End(xlDown)).Copy Sheets(3).Cells(1, 1)
           G = False
        Else
           Range(Sheets(2).Cells(2, 1), Sheets(2).Cells(2, 4).End(xlDown)).Copy Sheets(3).Cells(1, 1).End(xlDown).Offset(1, 0)
        End If
        Sheets(2).Cells(2, 3).AutoFilter
    Exit For
    Next i
Next K
Sheets(3).Select
Range(Sheets(3).Cells(2, 1), Sheets(3).Cells(2, 4).End(xlDown)).RemoveDuplicates Columns:=1
Range(Sheets(3).Cells(2, 1), Sheets(3).Cells(2, 4).End(xlDown)).Font.Color = RGB(0, 0, 0)
Application.ScreenUpdating = True
End Sub

TOP

¦^´_ 33# °a¤ªºµ

·PÁ°a¤ªºµ§ï¶iµ{¦¡½X

¸ê®ÆÁÙ®t¤@¨Ç

§Aªºµ{¦¡°õ¦æ§¹«á¥²¶·§R°£­«½Æ¶µ¥Ø

ªþ¤W§Ú³Ì²×»Ý­nªºÅã¥Ü®ÄªG»P­ã¤j¤ñ¸û¡A³o¼Ë¤ñ¸û®e©öÆ[¬Ý

¦C¥X§ó¦h¸ê®ÆV7¸ê®Æ§e²{.zip (49.29 KB)

TOP

¥»©«³Ì«á¥Ñ °a¤ªºµ ©ó 2020-8-24 22:41 ½s¿è

¦^´_ 34# qaqa3296
¨S¿ìªk¤F ·Q¤£¥X¨Ó ¤å¦rªº®æ¦¡¤Ó½ÆÂø¤F...  ¦pªG¬O¥Î asc ¤èªk ·|¼g±o§óªø....¥u¯à­É¥Î ­ã´£³¡ªL¤j¤j ÁÙ¦³ n7822123¤j¤j ­ÌªºÅÞ¿è...¤~¯à¿ì¨ì >"<
Public Sub ¼Ò½k¿z¿ï()

Application.ScreenUpdating = False
Range(Sheets(2).Cells(2, 1), Sheets(2).Cells(2, 4).End(xlDown)).Font.Color = RGB(255, 0, 0)
G = True
Sheets(3).Select
Sheets(3).Range(Cells(1, 1), Cells(1, 4).End(xlDown)).Clear
Sheets(2).Select

For K = 2 To Cells(2, 5).End(xlDown).Row
    X = Trim(Sheets(1).Cells(K, 3))
    If Sheets(1).Cells(K, 1) = "" Then Exit For
   
    For i = 2 To Cells(2, 3).End(xlDown).Row '¨Ì±ø¥ó¿z¿ï

        If X <> "" Then
        
            Sheets(2).Cells(2, 3).AutoFilter
            Cells(i, 1).AutoFilter Field:=3, Criteria1:="=*" & Mid(X, 1, 8) & "*", Operator:=xlOr, Criteria2:="=" & X & ""
            Range(Sheets(2).Cells(2, 1), Sheets(2).Cells(2, 4).End(xlDown)).Font.Color = RGB(0, 0, 0)

            Cells(i, 1).AutoFilter Field:=3, Criteria1:="=*" & Mid(X, 1, 5) & "*", Operator:=xlOr, Criteria2:="=" & X & ""
            Range(Sheets(2).Cells(2, 1), Sheets(2).Cells(2, 4).End(xlDown)).Font.Color = RGB(0, 0, 0)
            
            If X Like "####[-.]*" Or X Like "####[A-Z]*" Then
                Cells(i, 1).AutoFilter Field:=3, Criteria1:="=*" & Mid(X, 1, 4) & "*", Operator:=xlOr, Criteria2:="=" & X & ""
                Range(Sheets(2).Cells(2, 1), Sheets(2).Cells(2, 4).End(xlDown)).Font.Color = RGB(0, 0, 0)
            End If

            If ActiveSheet.AutoFilter.Range.SpecialCells(xlCellTypeVisible).Areas.Count = 1 Then
                Sheets(2).Cells(2, 3).AutoFilter
                Cells(i, 1).AutoFilter Field:=2, Criteria1:="=*" & X & "*", Operator:=xlOr, Criteria2:="=" & X & ""
                Range(Sheets(2).Cells(2, 1), Sheets(2).Cells(2, 4).End(xlDown)).Font.Color = RGB(0, 0, 0)
               
                Cells(i, 1).AutoFilter Field:=2, Criteria1:=Sheets(1).Cells(K, 2)
                Range(Sheets(2).Cells(2, 1), Sheets(2).Cells(2, 4).End(xlDown)).Font.Color = RGB(0, 0, 0)
            End If
        End If
         
        If X = "" Then
            X = Trim(Sheets(1).Cells(K, 2))
            Cells(i, 1).AutoFilter Field:=2, Criteria1:="=*" & X & "*", Operator:=xlOr, Criteria2:=Sheets(1).Cells(K, 2)
            Range(Sheets(2).Cells(2, 1), Sheets(2).Cells(2, 4).End(xlDown)).Font.Color = RGB(0, 0, 0)
            If ActiveSheet.AutoFilter.Range.SpecialCells(xlCellTypeVisible).Areas.Count = 1 Then
                Sheets(2).Cells(2, 3).AutoFilter
                Cells(i, 1).AutoFilter Field:=1, Criteria1:=Sheets(1).Cells(K, 1)
                Range(Sheets(2).Cells(2, 1), Sheets(2).Cells(2, 4).End(xlDown)).Font.Color = RGB(0, 0, 0)
            If ActiveSheet.AutoFilter.Range.SpecialCells(xlCellTypeVisible).Areas.Count = 1 Then
            Exit For
            End If
                Range(Sheets(2).Cells(2, 1), Sheets(2).Cells(2, 4).End(xlDown)).Font.Color = RGB(0, 0, 0)
            End If
        End If
        
    Exit For
    Next i
   
    If G = True Then
        Range(Sheets(2).Cells(1, 1), Sheets(2).Cells(1, 4).End(xlDown)).Copy Sheets(3).Cells(1, 1)
        G = False
    Else
        Range(Sheets(2).Cells(2, 1), Sheets(2).Cells(2, 4).End(xlDown)).Copy Sheets(3).Cells(1, 1).End(xlDown).Offset(1, 0)
    End If
    Sheets(2).Cells(2, 3).AutoFilter

Next K
Sheets(3).Select
Range(Sheets(3).Cells(2, 1), Sheets(3).Cells(2, 4).End(xlDown)).RemoveDuplicates Columns:=1
Range(Sheets(3).Cells(2, 1), Sheets(3).Cells(2, 4).End(xlDown)).Font.Color = RGB(0, 0, 0)
Application.ScreenUpdating = True
End Sub

TOP

¦^´_ 35# °a¤ªºµ

¥Ø«e´ú¸Õ

¦pªG³W®æ¬°ªÅ¥Õ(§Ñ°O¥´)¡A¨Ì·íªì©wªº±ø¥ó¨Ó¬Ý¡A¥u·|¦C¥X¨º¦Cªº¸ê®Æ¡A¦ý¬Ý°_¨Ó§A§â¸Ó¦C¦³¬Û¦P«~¦WªºÃöÁä¦r¥þ³¡§ä¥X¨Ó¤F¡C

TOP

¦^´_ 36# qaqa3296

¦A¥[¤@­Ó §PÂ_À³¸Ó¥i¥H¤F §A¸Õ¸Õ¬Ý

Public Sub ¼Ò½k¿z¿ï()

Application.ScreenUpdating = False
Range(Sheets(2).Cells(2, 1), Sheets(2).Cells(2, 4).End(xlDown)).Font.Color = RGB(255, 0, 0)
G = True
Sheets(3).Select
Sheets(3).Range(Cells(1, 1), Cells(1, 4).End(xlDown)).Clear
Sheets(2).Select

For K = 2 To Cells(2, 5).End(xlDown).Row
    X = Trim(Sheets(1).Cells(K, 3))
    If Sheets(1).Cells(K, 1) = "" Then Exit For
   
    For i = 2 To Cells(2, 3).End(xlDown).Row '¨Ì±ø¥ó¿z¿ï

        If X <> "" And Sheets(1).Cells(K, 3) <> "" Then
        
            Sheets(2).Cells(2, 3).AutoFilter
            Cells(i, 1).AutoFilter Field:=3, Criteria1:="=*" & Mid(X, 1, 8) & "*", Operator:=xlOr, Criteria2:="=" & X & ""
            Range(Sheets(2).Cells(2, 1), Sheets(2).Cells(2, 4).End(xlDown)).Font.Color = RGB(0, 0, 0)

            Cells(i, 1).AutoFilter Field:=3, Criteria1:="=*" & Mid(X, 1, 5) & "*", Operator:=xlOr, Criteria2:="=" & X & ""
            Range(Sheets(2).Cells(2, 1), Sheets(2).Cells(2, 4).End(xlDown)).Font.Color = RGB(0, 0, 0)
            
            If X Like "####[-.]*" Or X Like "####[A-Z]*" Then
                Cells(i, 1).AutoFilter Field:=3, Criteria1:="=*" & Mid(X, 1, 4) & "*", Operator:=xlOr, Criteria2:="=" & X & ""
                Range(Sheets(2).Cells(2, 1), Sheets(2).Cells(2, 4).End(xlDown)).Font.Color = RGB(0, 0, 0)
            End If

            If ActiveSheet.AutoFilter.Range.SpecialCells(xlCellTypeVisible).Areas.Count = 1 Then
                Sheets(2).Cells(2, 3).AutoFilter
                Cells(i, 1).AutoFilter Field:=2, Criteria1:="=*" & X & "*", Operator:=xlOr, Criteria2:="=" & X & ""
                Range(Sheets(2).Cells(2, 1), Sheets(2).Cells(2, 4).End(xlDown)).Font.Color = RGB(0, 0, 0)
               
                Cells(i, 1).AutoFilter Field:=2, Criteria1:=Sheets(1).Cells(K, 2)
                Range(Sheets(2).Cells(2, 1), Sheets(2).Cells(2, 4).End(xlDown)).Font.Color = RGB(0, 0, 0)
            End If
        End If
         
        If X = "" Then
            X = Trim(Sheets(1).Cells(K, 2))
            Cells(i, 1).AutoFilter Field:=2, Criteria1:="=*" & X & "*", Operator:=xlOr, Criteria2:=Sheets(1).Cells(K, 2)
            Range(Sheets(2).Cells(2, 1), Sheets(2).Cells(2, 4).End(xlDown)).Font.Color = RGB(0, 0, 0)
            If ActiveSheet.AutoFilter.Range.SpecialCells(xlCellTypeVisible).Areas.Count = 1 Then
                Sheets(2).Cells(2, 3).AutoFilter
                Cells(i, 1).AutoFilter Field:=1, Criteria1:=Sheets(1).Cells(K, 1)
                Range(Sheets(2).Cells(2, 1), Sheets(2).Cells(2, 4).End(xlDown)).Font.Color = RGB(0, 0, 0)
            If ActiveSheet.AutoFilter.Range.SpecialCells(xlCellTypeVisible).Areas.Count = 1 Then
            Exit For
            End If
                Range(Sheets(2).Cells(2, 1), Sheets(2).Cells(2, 4).End(xlDown)).Font.Color = RGB(0, 0, 0)
            End If
        End If
        
    Exit For
    Next i
   
    If G = True Then
        Range(Sheets(2).Cells(1, 1), Sheets(2).Cells(1, 4).End(xlDown)).Copy Sheets(3).Cells(1, 1)
        G = False
    Else
        Range(Sheets(2).Cells(2, 1), Sheets(2).Cells(2, 4).End(xlDown)).Copy Sheets(3).Cells(1, 1).End(xlDown).Offset(1, 0)
    End If
    Sheets(2).Cells(2, 3).AutoFilter

Next K
Sheets(3).Select
Range(Sheets(3).Cells(2, 1), Sheets(3).Cells(2, 4).End(xlDown)).RemoveDuplicates Columns:=1
Range(Sheets(3).Cells(2, 1), Sheets(3).Cells(2, 4).End(xlDown)).Font.Color = RGB(0, 0, 0)
Application.ScreenUpdating = True
End Sub

TOP

¦^´_ 36# qaqa3296

¦pªG¬O ³W®æ ©Î¬O «~¦W ¨ä¤¤¤@­Ó§Ñ°O¥´
Public Sub ¼Ò½k¿z¿ï()

Application.ScreenUpdating = False
Range(Sheets(2).Cells(2, 1), Sheets(2).Cells(2, 4).End(xlDown)).Font.Color = RGB(255, 0, 0)
G = True
Sheets(3).Select
Sheets(3).Range(Cells(1, 1), Cells(1, 4).End(xlDown)).Clear
Sheets(2).Select

For K = 2 To Cells(2, 5).End(xlDown).Row
    X = Trim(Sheets(1).Cells(K, 3))
    If Sheets(1).Cells(K, 1) = "" Then Exit For
   
    For i = 2 To Cells(2, 3).End(xlDown).Row '¨Ì±ø¥ó¿z¿ï

        If X <> "" And Sheets(1).Cells(K, 3) <> "" Then
            
            Sheets(2).Cells(2, 3).AutoFilter
            Cells(i, 1).AutoFilter Field:=3, Criteria1:="=*" & Mid(X, 1, 8) & "*", Operator:=xlOr, Criteria2:="=" & X & ""
            Range(Sheets(2).Cells(2, 1), Sheets(2).Cells(2, 4).End(xlDown)).Font.Color = RGB(0, 0, 0)

            Cells(i, 1).AutoFilter Field:=3, Criteria1:="=*" & Mid(X, 1, 5) & "*", Operator:=xlOr, Criteria2:="=" & X & ""
            Range(Sheets(2).Cells(2, 1), Sheets(2).Cells(2, 4).End(xlDown)).Font.Color = RGB(0, 0, 0)
            
            If X Like "####[-.]*" Or X Like "####[A-Z]*" Then
                Cells(i, 1).AutoFilter Field:=3, Criteria1:="=*" & Mid(X, 1, 4) & "*", Operator:=xlOr, Criteria2:="=" & X & ""
                Range(Sheets(2).Cells(2, 1), Sheets(2).Cells(2, 4).End(xlDown)).Font.Color = RGB(0, 0, 0)
            End If

            If Sheets(1).Cells(K, 2) <> "" Then
                 If ActiveSheet.AutoFilter.Range.SpecialCells(xlCellTypeVisible).Areas.Count = 1 Then
                     Sheets(2).Cells(2, 3).AutoFilter
                     Cells(i, 1).AutoFilter Field:=2, Criteria1:="=*" & X & "*", Operator:=xlOr, Criteria2:="=" & X & ""
                     Range(Sheets(2).Cells(2, 1), Sheets(2).Cells(2, 4).End(xlDown)).Font.Color = RGB(0, 0, 0)
                     
                     Cells(i, 1).AutoFilter Field:=2, Criteria1:=Sheets(1).Cells(K, 2)
                     Range(Sheets(2).Cells(2, 1), Sheets(2).Cells(2, 4).End(xlDown)).Font.Color = RGB(0, 0, 0)
                 End If
            End If
            
            
        End If
         
        If X = "" Then
            X = Trim(Sheets(1).Cells(K, 2))
            Cells(i, 1).AutoFilter Field:=2, Criteria1:="=*" & X & "*", Operator:=xlOr, Criteria2:=Sheets(1).Cells(K, 2)
            Range(Sheets(2).Cells(2, 1), Sheets(2).Cells(2, 4).End(xlDown)).Font.Color = RGB(0, 0, 0)
            If ActiveSheet.AutoFilter.Range.SpecialCells(xlCellTypeVisible).Areas.Count = 1 Then
                Sheets(2).Cells(2, 3).AutoFilter
                Cells(i, 1).AutoFilter Field:=1, Criteria1:=Sheets(1).Cells(K, 1)
                Range(Sheets(2).Cells(2, 1), Sheets(2).Cells(2, 4).End(xlDown)).Font.Color = RGB(0, 0, 0)
            If ActiveSheet.AutoFilter.Range.SpecialCells(xlCellTypeVisible).Areas.Count = 1 Then
            Exit For
            End If
                Range(Sheets(2).Cells(2, 1), Sheets(2).Cells(2, 4).End(xlDown)).Font.Color = RGB(0, 0, 0)
            End If
        End If
        
    Exit For
    Next i
   
    If G = True Then
        Range(Sheets(2).Cells(1, 1), Sheets(2).Cells(1, 4).End(xlDown)).Copy Sheets(3).Cells(1, 1)
        G = False
    Else
        Range(Sheets(2).Cells(2, 1), Sheets(2).Cells(2, 4).End(xlDown)).Copy Sheets(3).Cells(1, 1).End(xlDown).Offset(1, 0)
    End If
    Sheets(2).Cells(2, 3).AutoFilter

Next K
Sheets(3).Select
Range(Sheets(3).Cells(2, 1), Sheets(3).Cells(2, 4).End(xlDown)).RemoveDuplicates Columns:=1
Range(Sheets(3).Cells(2, 1), Sheets(3).Cells(2, 4).End(xlDown)).Font.Color = RGB(0, 0, 0)
Application.ScreenUpdating = True
End Sub

TOP

¦^´_ 38# °a¤ªºµ
¦pªG¬O «~¸¹ «~¦W ³W®æ ¼Æ¶q ¨ä¤¤¤@­Ó§Ñ°O¥´ ¥i¥H¸Õ¸Õ³o­Ó:)
Public Sub ¼Ò½k¿z¿ï()

Application.ScreenUpdating = False
Range(Sheets(2).Cells(2, 1), Sheets(2).Cells(2, 4).End(xlDown)).Font.Color = RGB(255, 0, 0)
G = True
Sheets(3).Select
Sheets(3).Range(Cells(1, 1), Cells(1, 4).End(xlDown)).Clear
Sheets(2).Select

For K = 2 To Sheets(1).Cells(2, 4).End(xlDown).Row
    X = Trim(Sheets(1).Cells(K, 3))
   
    If Sheets(1).Cells(K, 1) = "" And Sheets(1).Cells(K, 2) = "" And Sheets(1).Cells(K, 3) = "" And Sheets(1).Cells(K, 4) = "" Then
       Exit For
    End If
   
    For i = 2 To Cells(2, 3).End(xlDown).Row '¨Ì±ø¥ó¿z¿ï

        If X <> "" And Sheets(1).Cells(K, 3) <> "" Then
            
            Sheets(2).Cells(2, 3).AutoFilter
            Cells(i, 1).AutoFilter Field:=3, Criteria1:="=*" & Mid(X, 1, 8) & "*", Operator:=xlOr, Criteria2:="=" & X & ""
            Range(Sheets(2).Cells(2, 1), Sheets(2).Cells(2, 4).End(xlDown)).Font.Color = RGB(0, 0, 0)

            Cells(i, 1).AutoFilter Field:=3, Criteria1:="=*" & Mid(X, 1, 5) & "*", Operator:=xlOr, Criteria2:="=" & X & ""
            Range(Sheets(2).Cells(2, 1), Sheets(2).Cells(2, 4).End(xlDown)).Font.Color = RGB(0, 0, 0)
            
            If X Like "####[-.]*" Or X Like "####[A-Z]*" Then
                Cells(i, 1).AutoFilter Field:=3, Criteria1:="=*" & Mid(X, 1, 4) & "*", Operator:=xlOr, Criteria2:="=" & X & ""
                Range(Sheets(2).Cells(2, 1), Sheets(2).Cells(2, 4).End(xlDown)).Font.Color = RGB(0, 0, 0)
            End If

            If Sheets(1).Cells(K, 2) <> "" Then
                 If ActiveSheet.AutoFilter.Range.SpecialCells(xlCellTypeVisible).Areas.Count = 1 Then
                     Sheets(2).Cells(2, 3).AutoFilter
                     Cells(i, 1).AutoFilter Field:=2, Criteria1:="=*" & X & "*", Operator:=xlOr, Criteria2:="=" & X & ""
                     Range(Sheets(2).Cells(2, 1), Sheets(2).Cells(2, 4).End(xlDown)).Font.Color = RGB(0, 0, 0)
                     
                     Cells(i, 1).AutoFilter Field:=2, Criteria1:=Sheets(1).Cells(K, 2)
                     Range(Sheets(2).Cells(2, 1), Sheets(2).Cells(2, 4).End(xlDown)).Font.Color = RGB(0, 0, 0)
                 End If
            End If
            
            
        End If
         
        If X = "" Then
            X = Trim(Sheets(1).Cells(K, 2))
            Cells(i, 1).AutoFilter Field:=2, Criteria1:="=*" & X & "*", Operator:=xlOr, Criteria2:=Sheets(1).Cells(K, 2)
            Range(Sheets(2).Cells(2, 1), Sheets(2).Cells(2, 4).End(xlDown)).Font.Color = RGB(0, 0, 0)
            If ActiveSheet.AutoFilter.Range.SpecialCells(xlCellTypeVisible).Areas.Count = 1 Then
                Sheets(2).Cells(2, 3).AutoFilter
                Cells(i, 1).AutoFilter Field:=1, Criteria1:=Sheets(1).Cells(K, 1)
                Range(Sheets(2).Cells(2, 1), Sheets(2).Cells(2, 4).End(xlDown)).Font.Color = RGB(0, 0, 0)
            If ActiveSheet.AutoFilter.Range.SpecialCells(xlCellTypeVisible).Areas.Count = 1 Then
            Exit For
            End If
                Range(Sheets(2).Cells(2, 1), Sheets(2).Cells(2, 4).End(xlDown)).Font.Color = RGB(0, 0, 0)
            End If
        End If
        
    Exit For
    Next i
   
    If G = True Then
        Range(Sheets(2).Cells(1, 1), Sheets(2).Cells(1, 4).End(xlDown)).Copy Sheets(3).Cells(1, 1)
        G = False
    Else
        Range(Sheets(2).Cells(2, 1), Sheets(2).Cells(2, 4).End(xlDown)).Copy Sheets(3).Cells(1, 1).End(xlDown).Offset(1, 0)
    End If
    Sheets(2).Cells(2, 3).AutoFilter

Next K
Sheets(3).Select
Range(Sheets(3).Cells(2, 1), Sheets(3).Cells(2, 4).End(xlDown)).RemoveDuplicates Columns:=1
Range(Sheets(3).Cells(2, 1), Sheets(3).Cells(2, 4).End(xlDown)).Font.Color = RGB(0, 0, 0)
Application.ScreenUpdating = True
End Sub

TOP

¦^´_ 38# °a¤ªºµ

µ{¦¡«ä¸ô¨«°¾¤F¡C

­«¥ÓÃD¥Ø­nÂI¡G
¤£­n¥H«~¦W¬°°ò·Ç¬d¸ß¡A­«½Æ»P¦h¾l¸ê®Æ¤Ó¦h¨S¦³°Ñ¦Ò»ù­È
³W®æªÅ¥Õ¤S¤£¯à¬d«~¦W¡A¨ºÁÙ¦³¤°»ò¥i¥H¦C¡A©Ò¥H·Q¨ì¤F¶Ü?








À³¸Ó¥hÀˬdªº¬O§Aªº
If X = "" Then
¸Ì­±ªº°j°é

§ï§¹´N¨S¦³¤°»ò¤j°ÝÃD¤F¡Aµ{¦¡®Ö¤ß±µªñ»Ý¨D¡A¦ý¤£ª¾¦³¨S¦³¨ä¥LBUG¡C

¨S°ò¦­è¶}©l¾Ç²ß¡A¦ý¦n¹³¬ÝÀ´§Aµ{¦¡¦A°µ¤°»ò¤F¡AF8¦n¥Î

ºâ¬O¤¬¬Û¾Ç²ß§a

TOP

        ÀR«ä¦Û¦b : °µ¸Ó°µªº¨Æ¬O´¼¼z¡A°µ¤£¸Ó°µªº¨Æ¬O·Mè¡C
ªð¦^¦Cªí ¤W¤@¥DÃD