从电子表格X读取一个值，比较电子表格X和Y之间的相邻值

Question

我有一个基于宏的Excel文件，生成收到的项目列表及其状态(即收到，修复等)。这个程序每天运行，现在我让它捕获前一天的列表，并将其放在一个名为PreviousData的电子表格中，然后用当前的列表更新，该列表放在一个名为Data的电子表格中;这是用来比较我们认为我们在前一天固定/更改的状态。

我基本上是自学VBA，所以我不是超级高效或经验丰富。我想做的是:

1. 在数据电子表格中，获取从J2

开始的订单号

2. 切换到PreviousData电子表格，从步骤1中搜索订单号

场景A:如果在PreviousData上找到订单号，比较两个表上订单号旁边的状态值;如果它们不同，运行一些代码，否则什么都不做

场景B:如果PreviousData上没有找到订单号，什么都不做

1. 重复，直到在数据电子表格
中遇到第一个空白单元格

我在互联网上做了一些搜索，发现了一些东西(它可能已经从这个论坛，实际上)，将逐行和比较单元格值，但如果场景B出现的功能将失败与"超出范围。"以下是我尝试并修改的代码，以尝试工作:

Sub output()
Dim varSheetA As Variant
Dim varSheetB As Variant
Dim varSheetRMA As Variant
Dim strRangeToCheck As String
Dim strRangeRMA As String
Dim Variable As String
Dim iRow As Long
Dim iCol As Long
Dim Count As Integer
strRangeToCheck = "K2:L1000"
strRangeRMA = "J2:J1000"
' If you know the data will only be in a smaller range, reduce the size of the ranges above.
Debug.Print Now
varSheetA = Worksheets("PreviousData").Range(strRangeToCheck)
varSheetB = Worksheets("Data").Range(strRangeToCheck) ' or whatever your other sheet is.
varSheetRMA = Worksheets("Data").Range(strRangeRMA)
Debug.Print Now
Sheets("Data").Select
Range("J2").Select
Selection.Copy
Sheets("PreviousData").Select
Cells.Find(What:=Variable, After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Activate
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.
            ' Do nothing.
        Else
            ' Cells are different.
            ' Code goes here for whatever it is you want to do.
        End If
    Next iCol
Next iRow 
End Sub

请帮忙:)

Answer 1

这段代码应该更容易理解+它完成了工作。

Option Explicit
Sub CompareStatuses()
 Dim ws1 As Worksheet, ws2 As Worksheet, rng1 As Range, rng2 As Range
 Dim lr1&, lr2&, i&, j&
 Set ws1 = ThisWorkbook.Sheets("Data")
 Set ws2 = ThisWorkbook.Sheets("PreviousData")
 lr1 = ws1.Range("J" & Rows.Count).End(xlUp).Row
 lr2 = ws2.Range("J" & Rows.Count).End(xlUp).Row
 For i = 2 To lr1
  For j = 2 To lr2
     Set rng1 = ws1.Range("J" & i)
     Set rng2 = ws2.Range("J" & j)
     If StrComp(CStr(rng1.Value), CStr(rng2.Value), vbTextCompare) = 0 And _
        StrComp(CStr(rng1.Offset(0, 1).Value), CStr(rng2.Offset(0, 1).Value) _
         ,vbTextCompare) <> 0 Then
         ' found a matching Order + both statuses are different
         ' this is where you wanted to run some code
     End If
     Set rng1 = Nothing
     Set rng2 = Nothing
  Next j
 Next i
End Sub