request for VBA to find information in various cells

M

MAP

Active Member
Joined
Mar 22, 2007
Messages
315
Office Version
  1. 2007
Platform
  1. Windows
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

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Employee#
[/TD]
[TD][/TD]
[TD][/TD]
[TD]12345678[/TD]
[TD]Smith[/TD]
[TD]John[/TD]
[TD]Mr.[/TD]
[TD]01/02/1982[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]12345678
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]John Doe Smith[/TD]
[TD]12345678[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]Basic Information
[/TD]
[TD]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]FIRST NAME
[/TD]
[TD]John
[/TD]
[TD][/TD]
[TD]Mr. John Doe Smith[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]MIDDLE NAME
[/TD]
[TD]Doe
[/TD]
[TD][/TD]
[TD]123 Any Street[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]LAST NAME
[/TD]
[TD]Smith
[/TD]
[TD][/TD]
[TD]Apt #111[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]SUFFIX
[/TD]
[TD]Jr
[/TD]
[TD][/TD]
[TD]Houston, TX 77001[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15
[/TD]
[TD]DOB
[/TD]
[TD]01/02/1982
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18
[/TD]
[TD]SEX
[/TD]
[TD]Male
[/TD]
[TD][/TD]
[TD]Mr. John Doe Smith[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20
[/TD]
[TD]Physical Address
[/TD]
[TD]
[/TD]
[TD][/TD]
[TD]456 This Lane[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]Street1
[/TD]
[TD]123 Any Street
[/TD]
[TD][/TD]
[TD]-[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]22
[/TD]
[TD]Street2
[/TD]
[TD]Apt #111
[/TD]
[TD][/TD]
[TD]Dallas, TX 77007[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]23
[/TD]
[TD]City
[/TD]
[TD]Houston
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]24
[/TD]
[TD]State
[/TD]
[TD]TX
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]25
[/TD]
[TD]Zip
[/TD]
[TD]77001
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]30
[/TD]
[TD]Mailing Address
[/TD]
[TD]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]31
[/TD]
[TD]Street1
[/TD]
[TD]456 This Lane
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]32
[/TD]
[TD]Street2
[/TD]
[TD]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]33
[/TD]
[TD]City
[/TD]
[TD]Dallas
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]34
[/TD]
[TD]State
[/TD]
[TD]TX
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]35
[/TD]
[TD]ZIP
[/TD]
[TD]77007
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Sort by date Sort by votes
JLGWhiz, I thank you for your reply. FYI, the location of the data is not exactly in random places. It always appears in column A or B - like it is supposed to be. The row is the issue. The data may appear in +/- one or two rows.. but always next to its respective title.
 
Upvote 0

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
MAP said:
JLGWhiz, I thank you for your reply. FYI, the location of the data is not exactly in random places. It always appears in column A or B - like it is supposed to be. The row is the issue. The data may appear in +/- one or two rows.. but always next to its respective title.
Click to expand...
Then your illustration does not coincide with your narrative. I suggest that you get your thoughts organized and start a new thread with a clear description of what you need and a valid illustration or screen shot of the data that needs to be addressed.
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question Question
VBA : Copy only new information from specific cells in another worksheet
Replies
6
Views
901
kevin9999
K
C
  • Solved
VBA Code to move Amount Due from individual sheets to a master sheet, and then copy information from the master sheet back to the individual sheets?
Replies
7
Views
393
Coyotex3
C
C
  • Question Question
Finding & Extracting Senders Information (Name, Street Address, City, St, Zip) in the cell's content
Replies
2
Views
806
Chuckdee
C
C
  • Question Question
VBA macro that copies data from different sheets regardless of the sheet name
Replies
1
Views
475
My Aswer Is This
My Aswer Is This
GMC The Macro Man
  • Question Question
Clear Data without VBA
2
Replies
12
Views
4K
mumps
mumps

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top