|Before I post the code that I came up with, I want to point out a couple of things about your code, not as criticism, but only to help you write more efficient code.|
Rarely do you have to Select an object in VBA in order to perform an operation on it. In almost all cases you can simply refer to the object directly. For example, this snippet can be condensed to one instruction:
Set To_Be_Compared = Range("A1:" & Selection.Address)
This single instruction does the same thing:
Set To_Be_Compared = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
I typically split that into 2 instructions, especially if I want to use a "LastRow" variable multiple times in the code:
lastA_Rw = Range("A" & Rows.Count).End(xlUp).Row
Set To_Be_Compared = Range("A1:A" & lastA_Rw)
...can be reduced to single line:
Sheets(16).Cells(i, 4).Copy Sheets(16).Cells(i, 2)
However, there is no need to use the Copy operation in this case. Setting one cell equal to the other is a lot more efficient that Copy/Paste.
Sheets(16).Cells(i, 2) = Sheets(16).Cells(i, 4)
The main point here is that the code never actually Selects anything which makes the code much more efficient.
Another efficiency that you can take advantage of is the .Find method. Instead of looping through every cell in a range comparing the values one at a time, use the
.Find method to go directly to the value you are searching for. You will see the .Find method used in the code I offer below.
Another thing I noticed is your use of the Dim statement. I'm not sure why you used the Types that you did. For example, you used Dim i As String, yet you use the variable "i" as a number in a counter. It would make more sense to Dim i As Integer or Dim i as Long, with Long being just slightly faster based on how Excel deals with Integers.
I would also suggest Dim'ing your Ranges (To_Be_Compared, etc.) as Range, not as Variant. When a variable is Dim'd as Variant VBA has to do a lot more work when it encounters that variable to determine what type of data is stored in it before it can actually execute the instruction. You know that those variables will contain a Range, so you might as while Dim them as Range.
While each of these minor inefficiencies may not be noticeable in a fairly simplistic circumstance such as yours, each one does add up and will become more evident as your code gets more complicated. By using these "best practices" even with simple code, you'll be better positioned as your coding skills evolve and your code gets more intricate.
One last item...
You might want to review the debugging techniques offered in the following Tutorial. These techniques can be very helpful in not only determining why a piece of code doesn't work, but also in trying to understand how code that you find elsewhere does what it does. I learned most of my coding by reverse engineering other people's code via these debugging techniques.
OK, so finally, here is the code that I came up with for your task. If you have any questions, don't hesitate to ask.
Dim lastCode1_Rw As Long, lastCode2_Rw As Long
Dim c As Range, Code1 As Range
'Determine Last Row in Code1 and Code2 Columns
lastCode1_Rw = Range("A" & Rows.Count).End(xlUp).Row
lastCode2_Rw = Range("C" & Rows.Count).End(xlUp).Row
'Loop through Code1 Range searching for values in Code2 Range
'Copy Price for each match
For Each Code1 In Range("A2:A" & lastCode1_Rw)
With Range("C2:C" & lastCode2_Rw)
Set c = .Find(Code1, lookat:=xlWhole)
If Not c Is Nothing Then
Range("B" & Code1.Row) = c.Offset(0, 1)
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.