Attribute VB_Name = "module"
Private Const CompanyName As String = "jin,207,407"

Sub select_Click()
    Dim iRow As Integer
    Dim shearch_str As String
    Dim result_str As String
    Dim FirstAddr_str As String
    Dim NameArray As Variant
    NameArray = Split(CompanyName, ",")
    

    With ThisWorkbook.ActiveSheet
        For iRow = .UsedRange.Row To .UsedRange.Row + .UsedRange.Rows.Count
            shearch_str = Trim(.Cells(iRow, 1).Value)
            result_str = ""
            If shearch_str <> "" Then
                With ThisWorkbook.Sheets(2).Columns(1)
                '.Find(What:="A", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
                        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
                        , SearchFormat:=False)
                        'LookAt:=xlWhole,
                    Set c = .Find(What:=shearch_str, LookIn:=xlValues, LookAt:= _
                        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
                        , SearchFormat:=False)
                    If Not c Is Nothing Then
                        FirstAddr_str = c.Address
                        Do
                            result_str = result_str & c.Offset(ColumnOffset:=1).Value & ","
                            Set c = .FindNext(c)
                        Loop While c.Address <> FirstAddr_str
                    Else
                        result_str = "Not"
                    
                    End If
                End With
                
                .Cells(iRow, 2).NumberFormatLocal = "@"  '设置单元格式为文本
                .Cells(iRow, 2).Value = result_str
                For i = 0 To UBound(NameArray)
                    If shearch_str Like NameArray(i) & "-*" Then
                        .Cells(iRow, 2).Interior.ColorIndex = 43
                        Exit For
                    ElseIf result_str = "Not" Then
                        .Cells(iRow, 2).Interior.ColorIndex = 3
                        Exit For
                    End If
                Next
            End If
        Next
    End With
End Sub

Sub replace_Click()
    Dim iRow As Integer
    Dim shearch_str As String
    Dim result_str As String
    Dim FirstAddr_str As String
    Dim NameArray As Variant
    'NameArray = Split(CompanyName, ",")
    

    With ThisWorkbook.ActiveSheet
        For iRow = .UsedRange.Row To .UsedRange.Row + .UsedRange.Rows.Count
            shearch_str = Trim(.Cells(iRow, 1).Value)
            result_str = ""
            If shearch_str <> "" Then
                With ThisWorkbook.Sheets(2).Columns(1)
                '.Find(What:="A", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
                        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
                        , SearchFormat:=False)
                        'LookAt:=xlWhole,
                    Set c = .Find(What:=shearch_str, LookIn:=xlValues, LookAt:= _
                        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
                        , SearchFormat:=False)
                    If Not c Is Nothing Then
                        FirstAddr_str = c.Address
                        result_str = result_str & c.Offset(ColumnOffset:=1).Value
                    Else
                        result_str = "Not"
                    
                    End If
                End With
                
                .Cells(iRow, 2).NumberFormatLocal = "@"  '设置单元格式为文本
                .Cells(iRow, 2).Value = result_str
                If result_str = "Not" Then
                    .Cells(iRow, 2).Interior.ColorIndex = 3
                Else
                    .Cells(iRow, 2).Interior.ColorIndex = 43
                End If
            End If
        Next
    End With
End Sub