Compare columns and copy

danparaianu - Oct 8, 2011 at 01:21 PM
 danparaianu - Oct 9, 2011 at 04:23 AM
I need a Macro to do next : i have 2 sheets Sheet1 and Sheet2. In Sheet1 column A has a variable range of values i.e. A2 : A... Column B is empty. In Sheet 2 are 2 columns with variable length i.e. C5 : C... and D5 : D... The length of C5:C... is equal with D5:D... Now, i want to check value of every cell in range A from Sheet1 if is equal with one of cells from Sheet2 column C, and if is true, then copy the value of cell D with same range as C, to Sheet1 in the column B with the same range as A. i.e. If Sheet1 Range A20 = Sheet2 Range C45 then Sheet1 Range B20 = Sheet2 D45. How can i do that?

1 response

venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Oct 8, 2011 at 10:33 PM
suppose sheets 1 is like this

hdng1 hdng2

sheet 2 is like this
hdng1 hdng2
a 1
d 2
g 3

now in sheet 1 B2 copy this formula

this take care of 100 rows, of necessary change it.
copy B2 down.

where the entry in sheet 1 A is not available in sheet 2 column A you will get #N/A value.
you will get sheet 1
Hi venkat,
Your reply give me an idea. I renamed Sheet1 & Sheet2 with "1" & "2". In Sheet "1" i insert a button with this code:
Dim i As Integer
la = Worksheets("1").Range("A" & Rows.Count).End(xlUp).Row
lb = Worksheets("2").Range("B" & Rows.Count).End(xlUp).Row
For i = 5 To la
Ma = Application.VLookup(Sheets("1").Range("A" & i), Sheets("2"). Range("B4:B" & lb, "C4:C" & lb), 2, 0)
Worksheets("1"). Range("B" & i) = Ma
Next i
It works fine!
Thank you! All the best!