One sheet to another..
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!!! |
One sheet to another..
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. -- Vito ------------------------------------------------------------------------ Vito's Profile: http://www.excelforum.com/member.php...o&userid=29182 View this thread: http://www.excelforum.com/showthread...hreadid=558620 |
One sheet to another..
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 -- Regards, Tom Ogilvy "dan" wrote: 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!!! |
One sheet to another..
Thanks!!
Vito wrote: 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. -- Vito ------------------------------------------------------------------------ Vito's Profile: http://www.excelforum.com/member.php...o&userid=29182 View this thread: http://www.excelforum.com/showthread...hreadid=558620 |
All times are GMT +1. The time now is 12:38 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com