One sheet to another..

[dan]

How can I create a macro which will lookup values from one sheet in
another, and copy corresponding values? Example:

Sheet 1:
ColA ColB
Dogs
Cats
Pigs

Sheet 2
ColA ColB
Dogs 125
Gerbils 392
Cats 92
Pigs 423
Horses 32

I want it to look up if Sheet2 has 'Dogs' in column A, then to copy the
value in of Dogs in B2 (125)into the corresponding column in Sheet1
columnB where Dogs is listed. It would ignore gerbils and horses, but
put the values from Sheet2 in Sheet1 column B accordingly.

Thank you for your help in advance!!!

 

[Vito]

You shouldn't need a macro for that:

Just use Vlookup() function.

In Sheet1, B2 enter: =Vlookup(A2,Sheet2!A:B,2,0)

then copy the formula down column the column.

 

[Tom Ogilvy]

in column 2 of sheet1 (assume cell B1)

=if(A1="","",vlookup(A1,Sheet2!$A:$B,2,0))

then drag fill down.

if you want code

Sub DoLookups()
Dim rng as Range
With Worksheets("Sheet1")
set rng = .Range(.Cells(1,1),.Cells(1,1).End(xldown))
End with
rng.offset(0,1).formula = "=if(A1="""","""",vlookup(A1,Sheet2!$A:$B,2,0))"
rng.offset(0,1).Formula = rng.offset(0,1).Value
End Sub

 

[dan]

Thanks!!