CODEWITHSUNDEEP
excelvba
Francis Legault
Francis Legault

Reputation: 1

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

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 :/

Upvotes: 0

Views: 97

Answers (2)

Francis Legault
Francis Legault

Reputation: 1

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

Upvotes: 0

Error 1004
Error 1004

Reputation: 8220

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

Upvotes: 0

Related Questions