Solved VBA: Match value and copi-paste corresponding value

September 26, 2016 at 02:15:39
Specs: Windows 64

Hej,

The situation is in column 1 (Range("A")) there are code of the products applied for a particular client, in column 3 (Range("C")) there are the full list of the product codes, and column 4 (Range("D") consists of corresponding priced for codes in column3.

My code task is to match value of each cell from column 1 to column 3, and when it is equaled, copi and insert corresponding value from column 4 to column 2 exactly when it is equaled (not the entire row, only value). So that I get my list of codes and prices for the particular customer.

So far I tried this code and cannot find out how I copi-paste properly.

Sub Copipaste1()

Dim CompareRange As Variant, To_Be_Compared As Variant, x As Variant, y As Variant, i As String

Range("A1").Select
Selection.End(xlDown).Select
Set To_Be_Compared = Range("A1:" & Selection.Address)
Range("C1").Select
Selection.End(xlDown).Select

Set CompareRange = Range("C1:" & Selection.Address)

i = 1
To_Be_Compared.Select

For Each x In Selection
For Each y In CompareRange
If x = y Then
Sheet(16).Cells(i, 4).Copi
Sheet(16).Cells(i, 2).Select
ActiveSheet.Paste
i = i + 1
End If
Next y
Next x

End Sub

Thank you in advance


See More: VBA: Match value and copi-paste corresponding value

Reply ↓  Report •


✔ Best Answer
September 27, 2016 at 06:57:51
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:

Range("A1").Select
Selection.End(xlDown).Select
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)

This code...

Sheets(16).Cells(i, 4).Copy
Sheets(16).Cells(i, 2).Select
ActiveSheet.Paste

...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.

http://www.computing.net/howtos/sho...

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.

Sub CopyPrice()
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)
         End If
     End With
   Next
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
September 26, 2016 at 08:24:30
First, a posting tip:

Please click on the blue line at the end of this post and read the instructions on how to post VBA code in this forum so that it is easier for us to read. Then edit/repost your code so that it looks similar to the example found via that link. Thanks!

That said, there are at least 3 errors that I see in your code that may be causing problems. You did not specifically say what the problems were, so I'll just point out the errors in this section, which cause the code to fail immediately upon running:

Sheet(16).Cells(i, 4).Copi
Sheet(16).Cells(i, 2).Select

Errors 1 & 2:
The correct syntax is Sheets(), not Sheet()

Error 3:
It's Copy, not Copi

It should be:

Sheets(16).Cells(i, 4).Copy
Sheets(16).Cells(i, 2).Select

If those fixes solve your problem, let me know. If not, please tell me exactly what the problem is so that I know where to look next.

In addition, I have some suggestions to make your code more efficient, but I would prefer to wait until we at least get it working before we make it "better".

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •

#2
September 26, 2016 at 12:22:07
Hej again,

Thank you for posting guidance. I will try once more.

The problem which I encountered looks like following in the Excel sheet (I put "-" to keep the distance between the cells. The column B is empty:
-----A------------B---------C-----------------D
1 Code 1------Price------Code 2----------Price
2 2521014--------------316956-------------1,45
3 3801122--------------151147-------------1,25
4 3492613--------------1605677------------38,18
5 6561281--------------82367627-----------54,23
6 2534361--------------806934-------------17,93
7 7549104--------------82327802-----------94,69
8 5908682--------------82328805-----------39,59
9 151147---------------82368244-----------14,02

I would like to create a VBA code that delivers similar outcome as if I applied Excel built in function such as for cell B2= IFERROR(INDEX(D2:D9; MATCH(A2; C2:C9; 0); " ").

The mening is the values of each cells in the range A2:A9 should be matched with values of each cells in range C2:C1820 (I inserted only 9 lines in above example),and when the values is equaled such A9=C3, the corresponding value from cell D3 should be copy and pasted into cell B9. In the above example if A9=C3, so the value D3=1,25 copied into B9 so that B9 after procedure is executed equals to 1,25.The loop should go through all cells in Range("C2:C1820") to find each values from cells in range ("A2:A20") in my original task.

I am challenged by copy-paste part in that I don't now how to make looping with corresponding looping so that my code will copy value from one row and paste it in the other row.

So far my code look like

Sub Copypaste()

Dim CompareRange As Variant, To_Be_Compared As Variant, x As Variant, y As Variant, i As String

Range("A1").Select
Selection.End(xlDown).Select
Set To_Be_Compared = Range("A1:" & Selection.Address)
Range("C1").Select
Selection.End(xlDown).Select
Set CompareRange = Range("C1:" & Selection.Address)

i = 1
To_Be_Compared.Select

For Each x In Selection
For Each y In CompareRange
If x = y Then
Sheets(16).Cells(i, 4).Copy
Sheets(16).Cells(i, 2).Select
ActiveSheet.Paste
i = i + 1
End If
Next y
Next x

End Sub



Reply ↓  Report •

#3
September 26, 2016 at 13:12:35
I see that you did not follow my advice to use the pre tags to post your VBA code. The pre tags can be used to post VBA code and also to line up your data so that you don't have to use the dashes.


 
   	   A	       B	    C                 D
1	Code 1	      Price       Code 2            Price
2	2521014		          316956	     1,45
3 	3801122 	          151147	     1,25
4 	3492613		          1605677	    38,18
5	6561281	                  82367627	    54,23
6 	2534361 	          806934            17,93
7 	7549104		          82327802	    94,69
8 	5908682		          82328805	    39,59
9 	151147		          82368244	    14,02

Is there a reason that you don't want to use VLOOKUP?
=IFERROR(VLOOKUP(A2,$C$2:$D$9,2,0),"")

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •

Related Solutions

#4
September 27, 2016 at 00:47:12
Hej

Thank you for correction. I really tried to make it better.

Yes it is.

I started from this example just to settle my code up. Because later in my work the codes-to-be-compared and prices will not be found in the same Excel Sheet, but in different working books.

I have to proceed with hundreds of customers' codes to make my analyse regarding what they ordered this month. So what's why I am asking for help.


message edited by OlgaCPH


Reply ↓  Report •

#5
September 27, 2016 at 00:59:02
Moreover, each particular customer has its own buying history in separate Excel work book, while only one Excel book contains the all codes (to which I have to compare each customers order history).

I am not interested to merge the Excel books since I have to send information to each customer with analysis.


Reply ↓  Report •

#6
September 27, 2016 at 04:01:00
Ok, I will work on some code for you, which you will obviously need to modify for each changing situation.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •

#7
September 27, 2016 at 05:32:30
Thank you for helping.

Reply ↓  Report •

#8
September 27, 2016 at 06:57:51
✔ Best Answer
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:

Range("A1").Select
Selection.End(xlDown).Select
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)

This code...

Sheets(16).Cells(i, 4).Copy
Sheets(16).Cells(i, 2).Select
ActiveSheet.Paste

...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.

http://www.computing.net/howtos/sho...

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.

Sub CopyPrice()
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)
         End If
     End With
   Next
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •

#9
September 27, 2016 at 12:35:48
Hey,

Yes, agree, there are more efficient ways to proceed with coding. I went through your suggestions and your tutorials, which I consider professional. I am learning to perform coding into my analysis, so that I hope to improve with time.

Putting variables such as i as Variant and not String and similar mismatches I guess due to my last code was modified several times: first I marked equaled values, then tried looping and so on, until eventually I got lost and desperate, I asked for help in my post here. Very hilarious it might look when I read my first post but not for me at that time.

So I am very happy now not only because you wrote a fully functioning code for me but mostly for that you showed me how to think differently. I mean, for instance, code 1 in range "A".. with range "B".. and applying .find method.

Thank you for helping and efficient suggestions.


Reply ↓  Report •

#10
September 27, 2016 at 12:47:07
I glad it work for you. Let us know if you have any other questions.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •

#11
September 27, 2016 at 12:54:02
Ok thank you once again.

Reply ↓  Report •


Ask Question