Comparing data from 2 sheets and copying data based on results

Question

I have a workbook with 2 sheets that contain some of the same data. The first column in both worksheets contain a number assigned to an item, but sheet 2 contains more items

than sheet 1. Sheet 1 contains the items pertinent to me, so I am trying to copy the relevant data from sheet 2 into sheet 1.

For example:

Sheet 1
Column A
20
53
120
500
1123
etc

Sheet 2
Column A
1
2
3
4
5
etc

If the number in column A matches for both spreadsheets, I need to copy cell M from sheet 2 to cell I in sheet 1. I have tried a few different solutions posted elsewhere, but

since my data isn't ideally sorted between the two sheets, using things like VLookup wasn't working well.

I believe I need to store the information in column A in both sheets to an array and compare the data from there, I just have no clue how to write the code to continue

comparing the cell from sheet 1 until it finds a match in sheet 2, and then copy the data over.

Any help I can get would be greatly appreciated. Thanks everyone.

My current code:

Sub CopyFromSheet2()
Dim i As Long
Dim j As Long
Dim Range1 As Range
Dim Range2 As Range

Set Range1 = Sheets("Sheet1").Range("A:A")
Set Range2 = Sheets("Sheet2").Range("A:A")

For j = 1 To Range1
For I = 1 To Range2
If Sheets("Sheet1").Cells(i, "A").Value = Sheets("Sheet2").Cells(j, "A").Value Then
Sheets("Sheet1").Cells(i,"I").Value = Sheets("Sheet2").Cells(j, "M").Value
End If
Next i
Next j

End Sub

I am currently getting run time error 13 on the For j = 1 to Range1 line "Type mismatch"

gizlmo gizlmo · Accepted Answer · 2016-09-14T13:33:39

Something to start with would be a loop from row 1 to last row in sheet 1, then for each of these rows, compare value of cell 1 to each value in sheet 2.

A way to compare them to each other would be like this:

If Sheets("sheet 1").Cells(i, "A").Value = Sheets("sheet 2").Cells(j, "A").Value Then

now you just need to put a nested loop around this and you are good to go.

To copy column m to i:

Sheets("sheet 1").Cells(i, "I").Value = Sheets("sheet 2").Cells(j, "M").Value

Now try out something and feel free to ask again if you are running into an error

Comparing data from 2 sheets and copying data based on results

2 Answers