Compare cells in two different worksheets in a workbook and return a value to a next column

Question

I have two excel sheets and I need to do a cell comparison. Need a Macro solution.

Sheet 1 have column A-N and Sheet 2 have column A-S

I need to first check whether each column B values (B1:B2000) in sheet 1 available in Column F in Sheet 2. If available then select the value in column A in shee2 and paste that in the Column O in sheet 1.

Sorry for the detail question without putting any effort. Can't find anyway to enter to this question...

Refer this: Hopefully you will arrive at solution http://stackoverflow.com/questions/28107689/how-to-compare-columns-from-two-different-excel-workbooks — Tushar, Mar 05 '15 at 05:27
Thx @Avidan for the support link... I change the code to the one below with no success and no errors... Can some programming expert shed light on here? — Isu, Mar 05 '15 at 06:32
If you have code then you should edit it into your question, it should be pretty obvious that code-in-comments is unreadable without some sort of dark magic. With that in mind can you edit your code into your question using an actual code block (and with an explanation of what isn't working) so we can see what you've tried so far? Without some supporting information this reads like a request for someone to do your work for you, for which you'd need a freelance programmer, not a Q&A site. — Aiken, Mar 05 '15 at 08:16

score 1 · Accepted Answer · answered Mar 05 '15 at 07:13

Give this a go,

    Sub Button1_Click()
    Dim ws As Worksheet, sh As Worksheet
    Dim wsRws As Long, wsRng As Range, w As Range
    Dim shRws As Long, shRng As Range, s As Range

    Set ws = Sheets("Sheet1")
    Set sh = Sheets("Sheet2")

    With ws
        wsRws = .Cells(Rows.Count, "B").End(xlUp).Row
        Set wsRng = .Range(.Cells(1, "B"), .Cells(wsRws, "B"))
    End With

    With sh
        shRws = .Cells(Rows.Count, "F").End(xlUp).Row
        Set shRng = .Range(.Cells(1, "F"), .Cells(shRws, "F"))
    End With

    For Each w In wsRng
        For Each s In shRng

            If w = s Then w.Offset(0, -1) = s.Offset(0, -5)

        Next s
    Next w

End Sub

Compare cells in two different worksheets in a workbook and return a value to a next column

1 Answers1