Compare columns in two different Excel worksheets and workbooks, and then compare the values ​​in another column

Question

I have a lot of problems trying to understand this macro, so now I use 2 sheets in a book; I would like to use two different books, but this is not as important as the problem.

To explain, the column Sheet1 E is the id number and the column Sheet2 the id number, now if any value in Sheet1 E matches the value in column A in Sheet2, I need the macro to copy the corresponding cell from sheet 2 of column D. So what if E5 (from sheet 1) matches A1 in Sheet2, I need a macro to output Sheet2 D1 to Sheet1 F5. It would also be nice to delete the values ​​of the Sheet1 column if the Sheet1 E column does not match the value in the Sheet 2 column.

I have code, but it just inserts the values ​​from the column of the Sheet2 column, if the value of the E1 column of Sheet1 matches the value from the A sheet2 column. The problem is that the values ​​inserted from Sheet2 are inserted into the F1 column of sheet 1 and the values ​​do not match the correct value that it matches in Sheet2. They are just dumb. So, if the column Sheet1 E was like that

Sheet1 Column E Sheet1 F 1317 relays_120x120.jpg 1319 Control%20boards_120x120 1320 Control%20boards_120x120 Sheet2 Column A Sheet2 column D 1317 relays_120x120 1318 /relays_120x120 1319 ebay/SingleRunOval 

But in fact, I need them all to be equal, and if Sheet1 E has a value that is not in Sheet2, then do not send the link in Sheet1 F, leaving it blank.

Here is the code I have

 Sub FindMatches() Dim oldRow As Integer Dim newRow As Integer Dim i As Integer i = 1 For oldRow = 2 To 1170 For newRow = 1 To 1170 If Worksheets("Sheet1").Cells(oldRow, 5) = Worksheets("Sheet2").Cells(newRow, 1) Then Worksheets("Sheet1").Cells(i, 6) = Worksheets("Sheet2").Cells(oldRow, 4) i = i + 1 Exit For End If Next newRow Next oldRow End Sub 

Answer 1

It looks like you can complete your task using the VLookup function. Add this formula to Sheet1 F1: =IFERROR(VLookup(E1,Sheet2!A:D,4,FALSE),"")

This formula will copy the cell to sheet1 if a match is found, but if no match is found, the cell will remain empty.

Answer 2

Try using the code below. I just changed your code using the StrComp function

 Sub FindMatches() Dim oldRow As Integer Dim newRow As Integer Dim i As Integer i = 1 For oldRow = 1 To 1170 For newRow = 1 To 1170 If StrComp((Worksheets("Sheet1").Cells(oldRow, 5).Text), (Worksheets("Sheet2").Cells(newRow, 1).Text), vbTextCompare) <> 0 Then i = oldRow Worksheets("Sheet1").Cells(i, 6) = " " Else Worksheets("Sheet1").Cells(i, 6) = Worksheets("Sheet2").Cells(newRow, 4) i = i + 1 Exit For End If Next newRow Next oldRow End Sub