I'm having lots of trouble trying to figure out this macro, so right now I'm using 2 sheets in a workbook; I'd like to use two different workbooks but that isn't as important as the issue.
To explain, Sheet1 column E is the id number and in Sheet2 column A is the id number, now if any value in Sheet1 E matches a value in column A in Sheet2, I need the macro to copy the corresponding cell from Sheet 2 column D. So essentially if E5 (from sheet 1) matched A1 in Sheet2, I would need the macro to output Sheet2's D1 to Sheet1's F5. It would also be nice to delete the values of Sheet1 column F if Sheet1 column E doesn't match a value in Sheet 2 column A.
I have some code but it just pastes the values from Sheet2's column D if a value of Sheet1 column E matches a value from Sheet2 column A. The problem is when the values pasted from Sheet2 are pasted in Sheet1's column F and the values aren't matched with the correct value that it matched with in Sheet2. They are just dumbed. So if Sheet1 column E was like this
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 reality I need them all to be equal and if Sheet1 column E has a value that isnt in Sheet2 column, then dont post a link in Sheet1 column F leave 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
Compare 2 Excel workbooks Open the workbooks you want to compare. Go to the View tab, Window group, and click the View Side by Side button. That's it!
Sounds like you could accomplish your goal with the VLookup function. Add this formula to Sheet1 F1: =IFERROR(VLookup(E1,Sheet2!A:D,4,FALSE),"")
That formula will copy the cell into sheet1 if a match is found, but if no match is found the cell will remain blank.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With