首页 文章

在excel中使用vba进行excel vba工作表比较并将结果存储在工作表中?

提问于
浏览
0

我需要动态选择两个Excel工作表的范围,并逐行进行比较并使用excel VBA宏在报表单中将其打印为真或假 . 请帮助.. VBA Macro to compare all cells of two Excel files此链接很有帮助,但我想要动态选择范围,还需要在比较表中打印一些TRUE / FALSE .

我确实想要将工作表加载到variant数组,然后遍历它们以快速执行代码 .

注意 - 请假设需要比较的两个工作表包含相同的行数并进行排序 .

For iRow = LBound(varSheetA, 1) To UBound(varSheetA, 1)
    For iCol = LBound(varSheetA, 2) To UBound(varSheetA, 2)
        If varSheetA(iRow, iCol) = varSheetB(iRow, iCol) Then
            ' Cells are identical.
            ' i want to go to the exact cell in Comparison sheet and type TRUE Else
            ' Cells are different.
            ' i want to go to the exact cell in Comparison sheet and type FALSE
        End If
    Next iCol
Next iRow

1 回答

  • 1

    这是一个例子 . 示例代码仅记录比较选项卡中不匹配的数组(i,j)坐标 . 您应该修改它以记录您需要的任何其他信息:

    Sub CompareSheets()
        Dim s1 As Worksheet, s2 As Worksheet, s3 As Worksheet
        Dim rComp As Range, addy As String
        Dim I As Long, J As Long, K As Long
        Set s1 = Sheets("Sheet1")
        Set s2 = Sheets("Sheet2")
        Set s3 = Sheets("comparison")
        s1.Select
        Set rComp = Application.InputBox(Prompt:="Select range", Type:=8)
        addy = rComp.Address
        ary1 = rComp
        ary2 = s2.Range(addy)
        K = 1
        For I = LBound(ary1, 1) To UBound(ary1, 1)
            For J = LBound(ary1, 2) To UBound(ary1, 2)
                If ary1(I, J) = ary2(I, J) Then
                Else
                    s3.Cells(K, 1) = I
                    s3.Cells(K, 2) = J
                    K = K + 1
                End If
            Next J
        Next I
    End Sub
    

    EDIT:

    在回复您的评论时,此版本将使用TRUE和FALSE填充比较表:

    Sub CompareSheets2()
        Dim s1 As Worksheet, s2 As Worksheet, s3 As Worksheet
        Dim rComp As Range, addy As String
        Dim I As Long, J As Long, K As Long
        Set s1 = Sheets("Sheet1")
        Set s2 = Sheets("Sheet2")
        Set s3 = Sheets("comparison")
        s1.Select
        Set rComp = Application.InputBox(Prompt:="Select range", Type:=8)
        addy = rComp.Address
        ary1 = rComp
        ary2 = s2.Range(addy)
        ary3 = s3.Range(addy)
        K = 1
        For I = LBound(ary1, 1) To UBound(ary1, 1)
            For J = LBound(ary1, 2) To UBound(ary1, 2)
                If ary1(I, J) = ary2(I, J) Then
                    ary3(I, J) = "TRUE"
                Else
                    ary3(I, J) = "FALSE"
                End If
            Next J
        Next I
        s3.Range(addy) = ary3
    End Sub
    

相关问题