Compare Columns using VBA Macro

Question

I want to insert corresponding Column C values in Column D when Column A and Column B value matches.

For example:

Column A2 is equal to Column B2, now Column C2 value is posted on Column D2

OR

Column A7 is equal to Column B3 then Column C3 value is posted on Column D3

For detail please see the screen shot so you have idea what i am trying to do.

[Please click to see the screenshot][1]

The code which i am trying is below but it is not working properly, it is just giving only one cell value:

Private Sub ForComparing_Click()

Dim ws As Worksheet
Dim cel As Range
Dim lastRowA As Long, lastRowB As Long, lastRowC 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
    lastRowC = .Cells(.Rows.Count, "C").End(xlUp).Row 'last row of column C
    For Each cel In .Range("A2:A" & lastRowA)   'loop through column A
        'check if cell in column A exists in column B
        If WorksheetFunction.CountIf(.Range("B2:B" & lastRowB), cel) = 0 Then
            .Range("D" & cel.Row) = "No Match"
        Else
            .Range("D" & cel.Row) = .Range("C" & cel.Row)
        End If
    Next
End With

End Sub

Edited 1:

Please see the output of this code below: Click here to see screen shot

Column A3 should compare with Column B5 because the value D is equal in this case, and then it should print Column C5 value to Column D3

Furthermore, it should give value in Column D for every value of Column A but it stop after first 4 values.

Thanks for your time.

Edited 2:

Please see the screen shot

What you have just edited is perfectly right, but i want to do this for each Column A value.

I want to compare each Column A value with Column B and then corresponding Column C value is copied on Column D.

Answer 1

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.

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.

Answer 2

Sub compare()
Dim i As Integer
i = 1

Do While Cells(i, "A").Value <> ""
    If Cells(i, "A").Value <> Cells(i, "B").Value Then
        Cells(i, "C").Value = "No Match"
    Else
        Cells(i, "C").Value = "Match"
    End If


    i = i + 1
Loop

End Sub

Declare a counter (i) and then set a loop to iterate through Column A. The loop will keep going until a cell is found in column A that is blank.

Within the loop, for each row, you compare the 2 respective cells and change the value of the cell in Column C . Finally you add 1 to i on each iteration of the loop so it goes through each row.

An easier approach is without VBA using the below formula:

=IF(A1=B1,"Match", "No Match")