Compare cells value in sheet1 and sheet2 then output entire row of the cells with same value in sheet3

Question

Can you help me about my concern? I have 3 sheets , sheet1 ,sheet2 and sheet3 . I want to compare the excel value or text which is in sheet1 and sheet2. Sheet1 contains value from column A until C. But the only value need to compare to sheet 2 column A is the value in column A which is in sheet1. Once the both column (A in sheet1 and A in sheet2) was compared the same value The output will be displayed in sheet3 . Same value of two column (A sheet1 and sheet2) will be displayed included those in column B and C . my code is working but cannot include value in column B and C of the same value. Thanks for any help you can give me.

sheet1 :

columnA | columnB |    columnC

C2S3    |   L9     |    number 229 available

C2S14   |   LBS    |    number 221229 available

C2S17   |   LQP    |    number 222726 available


C3S90   |   LQSH2  |    number 222729182 available

C3S91   |   LQSH2  |    number 222729190 available

C3S9    |   PLR    |    number 262228 available

C3S10   |   RFFH   |    number 28161618 available

C3S11   |   BWT1s  |    number 123330129 available

sheet2 :

columnA |

C2S3

C2S14

C2S17

C3S1

C3S5

C3S9

C3S10

C3S11

C3S12

C3S14

C3S15

sheet3 output:

C2S3    |   L9    |    number 229 available

C2S14   |   LBS   |    number 221229 available

C2S17   |   LQP   |    number 222726 available



C3S9    |   PLR   |    number 262228 available

C3S10   |   RFFH  |    number 28161618 available

C3S11   |   BWT1s |    number 123330129 available

here's my code:

Private Sub CommandButton1_Click()

Dim oldRow As Integer

Dim newRow As Integer

Dim i As Integer

i = 1 

For oldRow = 1 To 300

    For newRow = 1 To 300

        If StrComp((Worksheets("Sheet1").Cells(oldRow, 1).Text),
        (Worksheets("Sheet2").Cells(newRow, 1).Text), vbTextCompare) <> 0 
        Then

             i = oldRow

             Worksheets("Sheet3").Cells(i, 3) = "" 'clear the cells

        Else

            Worksheets("Sheet3").Rows(newRow).Value = 
            Worksheets("Sheet2").Rows(newRow).Value 

            i = i + 1

            Exit For

        End If

    Next newRow

Next oldRow

MsgBox "Done Compare", vbOKOnly

End Sub

AlwaysData AlwaysData · Accepted Answer · 2017-04-28T02:32:53

Try this and see if it works. I had trouble following some of the logic in your code. I think that my suggestion should set columns A, B, and C of oldRow in Sheet3 to the values of oldRow in Sheet1.

Where you have these lines of code.

        Worksheets("Sheet3").Rows(newRow).Value = 
        Worksheets("Sheet2").Rows(newRow).Value

Try this and see if it works for you.

    Worksheets("Sheet3").Cells(oldRow, 1).Value = Worksheets("Sheet1").Cells(oldRow, 1).Value
    Worksheets("Sheet3").Cells(oldRow, 1).Offset(0, 1).Value = Worksheets("Sheet1").Cells(oldRow, 1).Offset(0, 1).Value
    Worksheets("Sheet3").Cells(oldRow, 1).Offset(0, 2).Value = Worksheets("Sheet1").Cells(oldRow, 1).Offset(0, 2).Value

Compare cells value in sheet1 and sheet2 then output entire row of the cells with same value in sheet3

1 Answers