工作表%1中的VBA数据与列A中的任何单元格匹配工作表%2

Question

我有两张表，报告和状态。我需要将报表中的单元格与Stat和Stat进行匹配。

我不知道我错过了什么：

我尝试使用For、If、IF Not进行循环

Sub Test1()
    Dim x As Integer
    Application.ScreenUpdating = False

    Rows = Range("A1", Range("A1").End(xlDown)).Rows.Count
    Range("A2").Select

    For x = 1 To Rows        
        If ActiveCell.Value = Stat.Range("A").Value Then ActiveCell.Offset(0, 11).Value = "Old"
        If Not ActiveCell.Value = Stat.Range("A").Value Then ActiveCell.Offset(0, 11).Value = "New"
        ' Selects cell down 1 row from active cell.
        ActiveCell.Offset(1, 0).Select
    Next

    Sheets("Stat").Select

    Rows2 = Range("A1", Range("A1").End(xlDown)).Rows.Count
    Range("A2").Select

    For x = 1 To Rows2
        If Not ActiveCell.Value = Report.Range("A").Value Then ActiveCell.Offset(0, 11).Value = "Cleared"
        ' Selects cell down 1 row from active cell.
        ActiveCell.Offset(1, 0).Select
    Next

    Application.ScreenUpdating = True
End Sub

我需要匹配A列中的所有单元格，并尝试在Stat Sheet中匹配A列中的任何单元格。

如果匹配，则向右偏移11个单元格报表，并向该单元格添加值“旧”。如果不匹配，则关闭报表中右侧的偏移量11单元格，并添加值"New“。

我需要做的最后一件事是匹配Stat Sheet中A列的所有单元格，并尝试与报表中A列的任何单元格匹配。

如果匹配，则什么也不做；如果不匹配，则向右添加工作表状态偏移量11，并添加“清除”值

我仍然在寻找这方面的工作，但不能弄清楚:/

Answer 1

这可能会对你有所帮助：

    Option Explicit

    Sub Loop_Loop()

        Dim LastrowReport As Long, LastrowStat As Long, i As Long, y As Long
        Dim ValueReport As String, ValueStat As String

        'Find Report sheet last row (Column A)
        LastrowReport = Sheet1.Cells(Sheet1.Rows.Count, "A").End(xlUp).Row
        'Find Stat sheet last row (Column A)
        LastrowStat = Sheet2.Cells(Sheet2.Rows.Count, "A").End(xlUp).Row

        'Loop value in sheet Report, column A (starting from second row)
        For i = 2 To LastrowReport
            'Value in sheet Report, Column A i row
            ValueReport = Sheet1.Range("A" & i).Value
            'Loop value in sheet Stat, column A (starting from second row)
            For y = 2 To LastrowStat
                'Value in sheet Stat, Column A y row
                ValueStat = Sheet2.Range("A" & y).Value
                'Check if ValueReport & ValueStat is equal
                If ValueReport = ValueStat Then
                    MsgBox "Same Values!"
                End If

            Next y
        Next i

    End Sub

Answer 2

错误4001

我听从了你的建议，但这次我不能让"Old“"New”和"Clear“这个词在同一个单元格中重叠:(

    Option Explicit

Sub Loop_Loop()

    Dim LastrowReport As Long, LastrowStat As Long, i As Long, y As Long
    Dim ValueReport As String, ValueStat As String


    LastrowReport = Sheet10.Cells(Sheet10.Rows.Count, "A").End(xlUp).Row

    LastrowStat = Sheet12.Cells(Sheet12.Rows.Count, "A").End(xlUp).Row


    For i = 2 To LastrowReport

        ValueReport = Sheet10.Range("A" & i).Value

        For y = 2 To LastrowStat

            ValueStat = Sheet12.Range("A" & y).Value

            If ValueReport = ValueStat Then
                Activecell.offset(0, 11).Value = "Old"
            If Not ValueReport = ValueStat Then
                Activecell.offset(0, 11).Value = "New"
            If Not ValueStat = ValueReport Then
                Activecell.offset(0, 11).Value = "Clear"
            End If

        Next y
    Next i

End Sub