Macro to compare two sheet and return a value

Ivy - Feb 14, 2011 at 07:38 AM
 ddbois - Nov 14, 2011 at 08:56 AM

Can you please help me with a macro. What I am trying to get is a macro that will compare two names in two sheets (Sheet1, col AD and Sheet2, col B) and return the corresponding value (Sheet2, col N) on the 1st sheet (col O). I have to repeat this for several names until there is a blank cell.

Can anyone help me on this. I'm a newbie and don't really understand how this work :(

2 responses

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Feb 17, 2011 at 08:19 AM
Why not use a vlookup ?
x = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
y = Sheets("Sheet2").Cells(Rows.Count, 6).End(xlUp).Row
For a = 2 To x
For b = 2 To y
If Sheets("Sheet1").Cells(a, 1) = Sheets("Sheet2").Cells(b, 2) Then

Sheets("Sheet1").Cells(a, 15) = Sheets("Sheet2").Cells(b, 14)
End If
Next b
Next a
End Sub

The x and y are the rows which to start on and the numbers after each a and b are the columns in which to match. So if sheet 1 A(1) = sheet 2 B(2) then sheet1 O(15) = sheet2 N(14)