获得2个不同表的行和列计数后的vlookup比较问题.结果中有1处不匹配

Question

在我的vba脚本中，我从2个不同的表中获得行和列计数，并使用Vlookup将这些值与每个表进行比较。比较结果正在更新，如未找到和找到。然而在1个结果中存在不匹配。有人能帮我一下吗?

 ExpectedDataRowCount = Sheets("Sheet1").Cells.SpecialCells(xlLastCell).Row
ExpectedDataColumnCount = Sheets("Sheet1").Cells.SpecialCells(xlLastCell).Column
ExpectedSrc = "R2C2:R" & ExpectedDataRowCount & "C" & ExpectedDataColumnCount
ActualDataRowCount = Sheets("Sheet2").Cells.SpecialCells(xlLastCell).Row
ActualDataColumnCount = Sheets("Sheet2").Cells.SpecialCells(xlLastCell).Column
Actualsrc = "R1C1:R" & ActualDataRowCountPlusone & "C" & ActualDataColumnCount
ActiveCell.FormulaR1C1 = _
        "=IF(ISNA(VLOOKUP([Actual_Value.xls]Sheet2!" & Actualsrc & _
           ",[Expected_Value.xlsx]Sheet1!" & ExpectedSrc & _
           ",1,FALSE)),""Not Found"",""Found"")"

Answer 1

我认为使用IF语句而不是依赖excel公式会对这种情况有好处。

你应该把你的:

 ActiveCell.FormulaR1C1 = _
        "=IF(ISNA(VLOOKUP([Actual_Value.xls]Sheet2!" & Actualsrc &_
 ",[Expected_Value.xlsx]Sheet1!" & ExpectedSrc & ",1,FALSE)),""Not Found"",""Found"")"

由:

For column = 1 to ActualDataColumnCount    
    For row = 1 to ActualDataRowCount
        For column1 = 1 to ExpectedDataColumnCountFor
             row1 = 1 to ExpectedDataRowCount
                IF Application.Workbooks("Actual_Value").Worksheets("Sheet2").Cells(row,column)_
                = Application.Workbooks("Expected_Value").Worksheets("Sheet1").Cells(row1,column1) Then
                   ActiveCell.Value = "Found"
                Else
                   ActiveCell.Value = "Not Found"
                End If
            next row1
        next column1
    next row
next column