request for macro to find information in various cells

[map]

originally posted on Mr. Excel... data table included at: http://www.mrexcel.com/forum/excel-questions/890493-request-visual-basic-applications-find-information-various-cells.html


I have a personal project that requires obtaining information from multiple cells in a worksheet and transferring that various information to other worksheets. I have no knowledge of macro, but I know what I need to have done must be be done with a macro.. I seek the help of the Excel gurus to offer me with a code (and hopefully explain how it works). I AM HOPING THE EXPERTS WILL HELP ME WITH THIS CHALLENGE.

Since there is no guarantee the information in a particular row for every instance, I need Excel to look for a string in Column A and then find the contents of the cell beneath (or adjacent to) that string.. and then make a list of that information on a different worksheet.

The information will be manually copied/pasted onto SHEET1 by me before I run the macro.. most of the relevant data will be in Column A and B

1) When the macro is run in SHEET1, I want Excel to look along columns A:B and find the Employee#, the Last name, the First name, the gender, and the date of birth and copy that data on a separate sheet (called LIST1). The next time the macro is run, Excel should copy the new information below the previous entry. Also, it would be good to have Excel notice the MALE or FEMALE in SHEET1 and convert that to "Mr." or "Ms."

2)When the macro is run in SHEET1, I also want Excel to form a similar list on worksheet LIST2. In this worksheet, I need Excel to concatenate the three names of the employee and enter it in one cell; and enter the employee# and the adjacent cell. Similar to LIST1, Excel should copy the new data below the previous entry.

3)In the worksheet FORM1, I want Excel to copy certain information from SHEET1 all concatenated in one cell. I intend to use this to print the address on envelopes. So in FORM1 in one particular cell, Excel should copy the three names; drop down a line; copy STREET1 and STREET2 with a space in between; drop a line; copy the CITY, STATE, and ZIP (with the necessary spaces and comma) or the "Primary Address". When the macro is run again, Excel should simply write the new information over the previous in that same cell.

4) In worksheet FORM2, the function will be similar to FORM1 except that on this worksheet, Excel uses the information found under "Mailing Address" in SHEET1


I have attempted to solicit responses from other Excel experts on Mr. Excel, but I have not received any replies.

http://www.mrexcel.com/forum/excel-questions/890493-request-visual-basic-applications-find-information-various-cells.html

I will appreciate your generous time and consideration if you would help me.

I thank you for your assistance and I truly appreciate your contribution to all persons learning Excel.

Sincerely,
MEHUL PARIKH

[snb]

I like challenges.
Who will pay you for the answer we'll provide ?

[map]

I like challenges.
Who will pay you for the answer we'll provide ?

I am not that experienced with Excel - I will not be compensated by anyone. I can only hope to obtain knowledge from what the experts share.

I am trying to learn macros and also trying to make it easier for me when I compile records or mail documents for personal use.

I hope you or your fellow experts can offer some assistance..