0

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"

Community
  • 1
  • 1
Tydis
  • 65
  • 1
  • 2
  • 9

2 Answers2

0

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

gizlmo
  • 1,882
  • 1
  • 14
  • 14
  • 1
    Thanks! I'll start with this and post back with an update. – Tydis Sep 14 '16 at 13:39
  • I've updated my code above. Please let me know what changes I should make to fix the runtime error 13. Thanks. – Tydis Sep 14 '16 at 15:37
  • my first guess is that it is because you are using a Range variable inside your for loops, instead of Range1 and Range2, you need an Integer, use Range1.Row for example – gizlmo Sep 15 '16 at 05:16
0

So I ended up consolidating the columns I need into 1 spreadsheet to make things easier, and I found this question on SO: Comparing two columns, and returning a specific adjacent cell in Excel which was very similar to what I was trying to do. The formula 

 =IFERROR(VLOOKUP(C1, A:B, 2, 0), "")

worked perfectly for me, so I am using that instead of the VBA scrip.

Community
  • 1
  • 1
Tydis
  • 65
  • 1
  • 2
  • 9