Help for Macro to VLookup in multiple files

[mogambo]

Hello Sirs/ madames,

I have a folder on my network drive. In this folder there are over 100 excel files. Each and every file will have a name in cell H2 and address in cell H3. Now, there would one more distinct file in the same folder. Let's say the filename is "home.xls". Home.xls will contain the list of all the names alongwith their respective addresses in cell range A1:B500, in those 100 other files.

What I am wanting to do is create some sort of a macro, if the name in cell H2 in all / any of these 100 files matches the name in cell range A1:A500 of "home.xls" (sort of vlookup) then return the text present in the next column of the match in cell range B1:B500. This should happen in all 100 files.

Basically the name in each of these 100 files will find a match in "home.xls" and I want to vlookup the values in cell range A1:B500 of "home.xls" and return the text in the second column (Column B) of "home.xls" to cell H3 of these 100 files.

I know this is very tough and so I am stuck and I could not find a way even to start this on my own. Can someone please help me to get this started ? I willing to work hard for getting this done.

Thanks you very much for your help :(

[mdmackillop]

This looks like a homework question. Please refer to our FAQ.

[mogambo]

It looks like no one including you knows the answer to the question and so a lame excuse. I am 40 years old for your information and I don't go to school anymore. Your aims are high on this forum but the help you give is too little and that too with PROOFING.

Its a pity I came here and requested help.

[Simon Lloyd]

Hello Sirs/ madames,

I have a folder on my network drive. In this folder there are over 100 excel files. Each and every file will have a name in cell H2 and address in cell H3. Now, there would one more distinct file in the same folder. Let's say the filename is "home.xls". Home.xls will contain the list of all the names alongwith their respective addresses in cell range A1:B500, in those 100 other files.

What I am wanting to do is create some sort of a macro, if the name in cell H2 in all / any of these 100 files matches the name in cell range A1:A500 of "home.xls" (sort of vlookup) then return the text present in the next column of the match in cell range B1:B500. This should happen in all 100 files.

Basically the name in each of these 100 files will find a match in "home.xls" and I want to vlookup the values in cell range A1:B500 of "home.xls" and return the text in the second column (Column B) of "home.xls" to cell H3 of these 100 files.

I know this is very tough and so I am stuck and I could not find a way even to start this on my own. Can someone please help me to get this started ? I willing to work hard for getting this done.

Thanks you very much for your help :(It would need some complex API wizardry to achieve what you want, either that or you would have to open each workbook in turn and then close before moving one, what you propose would take an absolute age to perform, the inefficiency of the task would probably render it useless to you!

[mogambo]

Thank you very much Simon.

What do you suggest ? How do I go about doing this thing ? Should I write a VLOOKUP() or INDEX(MATCH()) formula in these 100 files to update the values from "home.xls" ? But I will will have to do it manually in all these 100 files and again it is a very tiresome job. I hope you can give me some pointer to start with this job. I do not expect anyone to write a full program for me but some help in regards to initiate this work.

Thank you for the good help.

[lucas]

I think you blew any chance of getting help here.

You seem not to understand that the help here is voluntary and free. The least you could do when seeking free assistance is be civil.

Contrary to your opinion, many get good help here and are happy with the forum.

You can't come to a forum such as this and make demanding, demeaning remarks and expect people to rush to your assistance.