VBA Data in Sheet 1 matching with any cell in column A sheet 2

Question

I have two sheets Report and Stat. I need to match cell from Report with Stat and Stat with report.

I don't know what I'm missing :(

I try to loop with 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

I need to match all cells in column A and try to match with any cell in column A in Stat Sheet.

If it match then offset 11 cell Report sheet to the right and add value "Old" to the cell. If it doesn't match then Off offset 11 cell in Report sheet to the right and add value "New".

The last thing I need to match all cells in column A from Stat Sheet and try to match with any cell in column A in Report Sheet.

If it match then nothing If it doesn't match then sheet Stat offset 11 to the right and add value "Cleared"

I'm still looking working on this but can't figure it out :/

You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) • And row counting variables must be of type `Long` see `Dim x As Long` Excel has more rows than `Integer` can handle. — Pᴇʜ, Jan 22 '19 at 13:41
You use for both loop the same letter and you forget to mention the row. (If ActiveCell.Value = Stat.Range("A").Value Then --> If ActiveCell.Value = Stat.Range("A" & i).Value Then — Error 1004, Jan 22 '19 at 13:45

score 0 · Answer 1 · answered Jan 22 '19 at 13:37

This could help you:

    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

Could you please provide images for the two Sheets including before and after results? — Error 1004, Jan 22 '19 at 15:58
Nothing happen. I have 12 sheet in my workbook and Report and Stat are the two last one.. Also on your code we're missing the part to add " NEW, OLD, Cleared. I modify my code and post it like a answer :( I'm so noob to this thing! — Francis Legault, Jan 22 '19 at 16:46
my answer is a guideline how to get values and loop in both sheets. it is not the answer. — Error 1004, Jan 22 '19 at 16:51
I follow your guideline but still having issue... :( look my latest answers with your code! Can you help? — Francis Legault, Jan 22 '19 at 19:34

Francis Legault · Answer 2 · 2019-01-22T19:42:30.330

ERROR 4001

I follow your advise but this time I can't have "Old" "New" and "Clear" the word overlap in the same cell :(

    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

VBA Data in Sheet 1 matching with any cell in column A sheet 2

2 Answers2