[Solved] Compare Columns using VBA Macro

by


Try this

Option Explicit

Sub Demo()
    Dim ws As Worksheet
    Dim cel As Range
    Dim lastRowA As Long, lastRowB As Long

    Set ws = ThisWorkbook.Sheets("Sheet2")

    With ws
        lastRowA = .Cells(.Rows.Count, "A").End(xlUp).Row 'last row of column A
        lastRowB = .Cells(.Rows.Count, "B").End(xlUp).Row 'last row of column B
        For Each cel In .Range("A1:A" & lastRowA)   'loop through column A
            'check if cell in column A exists in column B
            If WorksheetFunction.CountIf(.Range("B1:B" & lastRowB), cel) = 0 Then
                .Range("C" & cel.Row) = "No Match"
            Else
                .Range("C" & cel.Row) = cel & " has match in column B"
            End If
        Next
    End With
End Sub

EDIT :

Option Explicit

Sub Demo()
    Dim ws As Worksheet
    Dim cel As Range, rngC As Range, rngB As Range
    Dim lastRowA As Long, lastRowB As Long

    Set ws = ThisWorkbook.Sheets("Sheet1")

    With ws
        lastRowA = .Cells(.Rows.Count, "A").End(xlUp).Row 'last row of column A
        lastRowB = .Cells(.Rows.Count, "B").End(xlUp).Row 'last row of column B
        For Each cel In .Range("A2:A" & lastRowB)   'loop through column B
            'check if cell in column A exists in column B
            If WorksheetFunction.CountIf(.Range("A2:A" & lastRowB), cel) = 0 Then
                .Range("D" & cel.Row) = "No Match"
            Else
                .Range("D" & cel.Row) = Application.WorksheetFunction.Index(.Range("C2:C" & lastRowB), Application.WorksheetFunction.Match(cel, .Range("B2:B" & lastRowB), 0), 1)
            End If
        Next
    End With
End Sub

See image for reference.

enter image description here

However I’m still very doubtful of what you are trying to achieve. You are matching only first 4 values from column A as mentioned by you in question “but it stop after first 4 values“. Still, as per my solution it will match 4 rows from Column A to Column B and if it matches then corresponding Column C values will be displayed in Column D. If there’s no match then Column D will display No Match.

14

solved Compare Columns using VBA Macro