vba - 比较excel并复制其余信息

Question

我正在优化代码，经过人们的审查，人们要求我使用 Option Explicit 并为所有内容定义变量并缩短代码。我做到了最大可能但是下面的代码通过询问路径从另一个excel复制数据并在V和W列中复制一些特定数据。还有一个公式可以比较数据并找到确切的行并且需要复制。

现在请帮助我应该如何优化此代码并为其提供变量。

或者请提供我们可以比较2个excel的代码，例如:A2:E相同，那么它应该复制H2:I

For Each ws In MainWB.Worksheets
    If ws.Name <> "Sap Data" And ws.Name <> "Automated BL Import" Then
        With MainWB.Worksheets(ws.Name)
            .Range("V1").Value = "When it will be Cleared or Action Taken/Required"
            .Range("W1").Value = "Backup Link"
            LastRow = MainWB.Worksheets(ws.Name).Range("B" & Rows.Count).End(xlUp).Row
            .Range("Q1:Q" & LastRow).Delete
        End With
    End If
Next ws
b = MsgBox("Do you want to update comments for current postings from previous month?" & vbCrLf & vbCrLf & "Note:- If are runing this macro for the 1st time plese choose option 'No'", _
           vbYesNo + vbQuestion, "Question")
If b = vbYes Then
    Filename = Application.GetOpenFilename(, , "Please select previous month BL comment file to update comments.", , False)
    If Filename <> "False" Then
        Workbooks.Open Filename, Format:=2
    End If
    updatesheet = ActiveWorkbook.Name
    For Each ws In MainWB.Sheets
        If ws.Name <> "Sap Data" And ws.Name <> "Automated BL Import" Then
            For Each ds In Workbooks(updatesheet).Sheets
                If ds.Name = ws.Name Then
                    LastRow = MainWB.Worksheets(ws.Name).Range("B" & Rows.Count).End(xlUp).Row
                    With MainWB.Worksheets(ws.Name)
                        .Range("T2:T" & LastRow).Formula = "=IFERROR(IF(VLOOKUP(RC[-1],'[" & updatesheet & "]" & ws.Name & "'!R2C[-1]:R1048576C,2,0) = 0,"""",VLOOKUP(RC[-1],'[" & updatesheet & "]" & ws.Name & "'!R2C[-1]:R1048576C,2,0)),"""")"
                        .Range("U2:U" & LastRow).Formula = "=IFERROR(IF(VLOOKUP(RC[-2],'[" & updatesheet & "]" & ws.Name & "'!R2C[-2]:R1048576C,3,0) = 0,"""",VLOOKUP(RC[-2],'[" & updatesheet & "]" & ws.Name & "'!R2C[-2]:R1048576C,3,0)),"""")"
                        .Range("V2:V" & LastRow).Formula = "=IFERROR(IF(VLOOKUP(RC[-3],'[" & updatesheet & "]" & ws.Name & "'!R2C[-3]:R1048576C,4,0) = 0,"""",VLOOKUP(RC[-3],'[" & updatesheet & "]" & ws.Name & "'!R2C[-3]:R1048576C,4,0)),"""")"
                        .Range("W2:W" & LastRow).Formula = "=IFERROR(IF(VLOOKUP(RC[-4],'[" & updatesheet & "]" & ws.Name & "'!R2C[-4]:R1048576C,5,0) = 0,"""",VLOOKUP(RC[-4],'[" & updatesheet & "]" & ws.Name & "'!R2C[-4]:R1048576C,5,0)),"""")"
                        .Range("X2:X" & LastRow).Formula = "=IFERROR(IF(VLOOKUP(RC[-5],'[" & updatesheet & "]" & ws.Name & "'!R2C[-5]:R1048576C,6,0) = 0,"""",VLOOKUP(RC[-5],'[" & updatesheet & "]" & ws.Name & "'!R2C[-5]:R1048576C,6,0)),"""")"
                        .Range("T2:X" & LastRow).Value = MainWB.Worksheets(ws.Name).Range("T2:X" & LastRow).Value
                    End With

Answer 1

你的底部是一团糟，你错过了一些结束，你错过了变量的暗淡

代码的第一部分如下。

您需要解释您要对代码的第二部分执行的操作。

    Sub Button1_Click()
    Dim wb As Workbook, ws As Worksheet
    Dim bk As Workbook, sh As Worksheet

    Set wb = Workbooks("ThisOne.xlsm")

    For Each ws In wb.Sheets
        If ws.Name <> "Sap Data" And ws.Name <> "Automated BL Import" Then
            With ws
                .Range("V1").Value = "When it will be Cleared or Action Taken/Required"
                .Range("W1").Value = "Backup Link"
                LastRow = ws.Range("B" & Rows.Count).End(xlUp).Row
                .Range("Q1:Q" & LastRow).Delete'?
            End With
        End If
    Next ws
    b = MsgBox("Do you want to update comments for current postings from previous month?" & vbCrLf & vbCrLf & "Note:- If are runing this macro for the 1st time plese choose option 'No'", _
               vbYesNo + vbQuestion, "Question")
    If b = vbYes Then
        Filename = Application.GetOpenFilename(, , "Please select previous month BL comment file to update comments.", , False)
        If Filename <> "False" Then
            Workbooks.Open Filename, Format:=2
        End If
    Else: Exit Sub
    End If
    Set bk = ActiveWorkbook

    ' updatesheet = ActiveWorkbook.Name'what is this for?
    For Each sh In bk.Sheets
        '        If sh.Name <> "Sap Data" And ws.Name <> "Automated BL Import" Then
        '            For Each ds In Workbooks(updatesheet).Sheets
        '                If ds.Name = ws.Name Then
        '                    LastRow = MainWB.Worksheets(ws.Name).Range("B" & Rows.Count).End(xlUp).Row
        '                    With MainWB.Worksheets(ws.Name)
        '                        .Range("T2:T" & LastRow).Formula = "=IFERROR(IF(VLOOKUP(RC[-1],'[" & updatesheet & "]" & ws.Name & "'!R2C[-1]:R1048576C,2,0) = 0,"""",VLOOKUP(RC[-1],'[" & updatesheet & "]" & ws.Name & "'!R2C[-1]:R1048576C,2,0)),"""")"
        '                        .Range("U2:U" & LastRow).Formula = "=IFERROR(IF(VLOOKUP(RC[-2],'[" & updatesheet & "]" & ws.Name & "'!R2C[-2]:R1048576C,3,0) = 0,"""",VLOOKUP(RC[-2],'[" & updatesheet & "]" & ws.Name & "'!R2C[-2]:R1048576C,3,0)),"""")"
        '                        .Range("V2:V" & LastRow).Formula = "=IFERROR(IF(VLOOKUP(RC[-3],'[" & updatesheet & "]" & ws.Name & "'!R2C[-3]:R1048576C,4,0) = 0,"""",VLOOKUP(RC[-3],'[" & updatesheet & "]" & ws.Name & "'!R2C[-3]:R1048576C,4,0)),"""")"
        '                        .Range("W2:W" & LastRow).Formula = "=IFERROR(IF(VLOOKUP(RC[-4],'[" & updatesheet & "]" & ws.Name & "'!R2C[-4]:R1048576C,5,0) = 0,"""",VLOOKUP(RC[-4],'[" & updatesheet & "]" & ws.Name & "'!R2C[-4]:R1048576C,5,0)),"""")"
        '                        .Range("X2:X" & LastRow).Formula = "=IFERROR(IF(VLOOKUP(RC[-5],'[" & updatesheet & "]" & ws.Name & "'!R2C[-5]:R1048576C,6,0) = 0,"""",VLOOKUP(RC[-5],'[" & updatesheet & "]" & ws.Name & "'!R2C[-5]:R1048576C,6,0)),"""")"
        '                        .Range("T2:X" & LastRow).Value = MainWB.Worksheets(ws.Name).Range("T2:X" & LastRow).Value
        '                    End With
        '                End If
        '            Next ds
        '        End If
    Next sh

End Sub