LP04 查找替换文件

Sub StartApprovalClick()
ActiveWorkbook.RefreshAll
With Task
    OpenTask.Columns(8).Copy Task.Cells(1, 1)
    rowTask = .Cells(5666, 1).End(xlUp).Row
    .Columns(2).Clear
    temRow = 0
    Compare.Cells.Clear
    If rowTask > 1 Then
        For i = 2 To rowTask
            linkTask = Task.Cells(i, 1).Hyperlinks.Item(1).Address
            With Approval
                rowApproval = .Cells(1048576, 1).End(xlUp).Row
                For j = 2 To rowApproval
                    If .Cells(j, 1).Hyperlinks.Item(1).Address = linkTask Then
                        documentVersion = CLng(.Cells(j, 10))
'                        If documentVersion = 1 Then
'                            Task.Cells(i, 2) = "New document"
'                        Else
                            
                            Call ApprovalProcess(i, temRow)
                            temRow = temRow + 4
'                        End If
                        Exit For
                    End If
                Next
            End With
        Next
    End If
End With
End Sub
Sub ApprovalProcess(i, temRow)

linkTask = Task.Cells(i, 1).Hyperlinks.Item(1).Address
With Approval
    rowApproval = .Cells(1048576, 1).End(xlUp).Row
    For j = 2 To rowApproval
        If .Cells(j, 1).Hyperlinks.Item(1).Address = linkTask Then
            .Rows(1).Copy Compare.Cells(temRow + 1, 1)
            .Rows(j).Copy Compare.Cells(temRow + 2, 1)
            Compare.Rows(temRow + 2).Interior.Color = 65535
            documentNumber = .Cells(j, 7)
            documentVersion = CLng(.Cells(j, 10))
            With LP04
                rowLP04 = .Cells(1048576, 1).End(xlUp).Row
                If documentVersion = 1 Then
                    Compare.Cells(temRow + 3, 1) = "New document"
                ElseIf documentNumber <> "" Then
                    Set rngTem = LP04.Range(LP04.Cells(2, 7), LP04.Cells(rowLP04, 7)).Find(documentNumber, LookIn:=xlValues)
                    If Not rngTem Is Nothing Then
                        .Rows(1).Copy Compare.Cells(temRow + 3, 1)
                        .Rows(rngTem.Row).Copy Compare.Cells(temRow + 4, 1)
                    Else
                        Compare.Cells(temRow + 3, 1) = documentNumber & "    Not found"
                    End If
'                    For w = 2 To rowLP04
'                        If .Cells(w, 7) = documentNumber Then
'                            .Rows(1).Copy Compare.Cells(temRow + 3, 1)
'                            .Rows(rngTem.Row).Copy Compare.Cells(temRow + 4, 1)
'                            Exit For
'                        End If
'                    Next
'                    If w = rowLP04 + 1 Then
'                        Compare.Cells(temRow + 3, 1) = documentNumber & "    Not found"
'                    End If
                Else
                    For v = 2 To rowLP04
                        If .Cells(v, 1) = Approval.Cells(j, 1) Then
                            .Rows(1).Copy Compare.Cells(temRow + 3, 1)
                            .Rows(v).Copy Compare.Cells(temRow + 4, 1)
                            Exit For
                        ElseIf .Cells(v, 3) = Approval.Cells(j, 3) And .Cells(v, 6) = Approval.Cells(j, 6) And .Cells(v, 5) = Approval.Cells(j, 5) _
                           And .Cells(v, 4) = Approval.Cells(j, 4) And .Cells(v, 2) = Approval.Cells(j, 2) Then
                            .Rows(1).Copy Compare.Cells(temRow + 3, 1)
                            .Rows(v).Copy Compare.Cells(temRow + 4, 1)
                            Exit For
'                                    ElseIf .Cells(v, 3) = Approval.Cells(j, 6) And .Cells(v, 6) = Approval.Cells(j, 9) And .Cells(v, 5) = Approval.Cells(j, 8) _
'                                       And .Cells(v, 4) = Approval.Cells(j, 7) And .Cells(v, 2) = Approval.Cells(j, 5) Then
'                                        .Rows(1).Copy Compare.Cells(3, 1)
'                                        .Rows(v).Copy Compare.Cells(4, 1)
'                                        Exit For
                        Else
                            Compare.Cells(temRow + 3, 1) = "No similar items"
                        End If
                    Next
                End If
            End With
            Exit For
        End If
    Next
End With
End Sub

 

上一篇:pytest文档20-pytest-html报告优化(添加Description)


下一篇:Revit记录需要涂黑的格子---供自己使用