Searching for and moving data within a range of cells

Hi everyone,

This is only my second post and i could really do with your help:

How do I get VB to go to a specific word within a column; then search within a predefined number of cells around itself to find the character '@'.

EG: The word 'Distance' is first found in A2 (including multiple places randomly down column A). The email address '[email protected]' could be anywhere within the range A2:B6. Once the email address is found I need VB to replace the cell containing 'distance' with the cell containing the email address. The process repeats for every cell containing 'distance' with the same predefined range (2 columns wide, 5 rows deep)

any help would be great. I would have tried to present some coding of my own but I have no Idea how it might look.

Cheers.

Tom

of course... see sample below

also, just to add I would like the same thing to happen where 'weekends' appear and replace it with the mobile number showing next to Contact Number.

I basically need the information to be displayed next to each other thus:

Column A Column B Column C Column D Column E ColumnF
NAME blank blank MOBILE NUMBER EMAIL ADDRESS REGISTERED/Trainee


cheers

Sorry, it was a bit rushed. I'll try again.

Sheet1 contains a sample of the unedited data, Sheet 2 shows my intended end result. I have tried recording Macros in an attempt to sort the data step by step (very clumsily I expect)and I've found some of the data does not go where I need it to be (ie nicely lined up in rows)and as a result some of it gets lost towards the final stages.

I would really appreciate some help with getting it accurate.

thanks

Tom

thanks for your reply. I've copied your code into a new module but nothing seems to happen. Any ideas as to what i should do?

thanks

tom

Hi,Patel45. Thanks a lot for that, I think you're nearly there! however with a larger amount of data it doesn't always get it quite right. Your code seems to assume that the data required will always be in the cell you have directed it to look in, sometimes it will be, but as you will see this is not always the case. The correct piece of information could be in the cell below it.

For example the presence of an additional telephone, or a blank cell instead of a telephone number or an email address (which is actually in the cell below). I have attached a new spreadsheet to show how your code has responded to a larger amount of data.I don't understand code enough to tweak it myself.

On the question of telephone numbers, ideally I would like to move only numbers starting with 07 and delete numbers starting with 01

If you can resolve these issues I would be very greatful.

thanks again.