Match numbers in range
 

Hi,
Can anyone tell me if you there is a macro which will carry out the following?
select value of C2 in current workbook , open another workbook ( unless it
is already open, then switch to it) and search a range of A21:A5000. when it
finds a number that matches, it needs to paste data into the cells in the
same row. I.E.

C2 = 2245 ( for example)
( there are other cells to be included but i can get around these)
open other workbook unless it is open already then swith to it,
search the range until C2 value matches the number 2245 in the list,
and copy data into the cells next to it which are columns B,C,E,F,G,H,I.

the workbook to be opened is called QUOTE REGISTER and the sheet in Quote
Register is Year To Date.

i need to transfer all data from hundreds of files to this sheet. If anyone
could help, well i would be so glad :)

Kind Regards,

nigel

Hi
Try using VLOOKUP. This will work on closed workbooks too. Have a look at
Help for some more information. The format is something like:
=VLOOKUP(C2,'C:\My Documents\[QUOTE REGISTER]'!A:E,2 FALSE)
This will take the value in C2 and search column A in the QUOTE REGISTER
book for it. When it finds it, it will return the value that is in the cell
that is 2 to the right of it.

--
Andy.


"Nigel" wrote in message
...
Hi,
Can anyone tell me if you there is a macro which will carry out the
following?
select value of C2 in current workbook , open another workbook ( unless it
is already open, then switch to it) and search a range of A21:A5000. when
it
finds a number that matches, it needs to paste data into the cells in the
same row. I.E.

C2 = 2245 ( for example)
( there are other cells to be included but i can get around these)
open other workbook unless it is open already then swith to it,
search the range until C2 value matches the number 2245 in the list,
and copy data into the cells next to it which are columns B,C,E,F,G,H,I.

the workbook to be opened is called QUOTE REGISTER and the sheet in Quote
Register is Year To Date.

i need to transfer all data from hundreds of files to this sheet. If
anyone
could help, well i would be so glad :)

Kind Regards,

nigel