Find Text and copy adjacent cells

Hi everybody,

I'm trying to work out a macro to find text with in a cell in this case "Main Crop" and copy the cell to the right and the cell to the left into 2 different cells of a newly opened spreadsheet.
Along with finding 3 other cells with text in them to the spreadsheet as well.

Much thanks in advance for any help.

Chris.

Hi..

Can you attach a sample Workbook showing the column with the text that you need to search through to find the string "Main Crop"..

Along with finding 3 other cells with text in them to the spreadsheet as well.

What do you mean by this..? Do you need to also search through the cells in the column and do the same if it finds any of the 3 other strings?.. if so.. what are these other strings you need to search for?

Apo,

The Main Crop info is in different cells in the E Column. I'd also like it to put the Crop in the row (i.e Cell E10) in column a, then the area to the left of the main crop in column b, then the total plants to the right of the Main Crop in column c and then Harvest Delivery total (i.e Cell F36)in column d. The main problem I can think of is that I need these cells for each different crop and the information varies the cells for each crop. The attached file is only a base sample and the actual spreadsheet can be more than 3000 rows of info. I need it to filter out all crops with these fields so I can then do calculations on each row.

Cheers,
Chris.

k.. the requirements are clear.. I'll see what i can come up with..

Thanks Apo can't wait to hear from you.

Hi..

Here's one way to do it.. string a few Find Next functions together and output values using Offset to sheet2. Then create a new Workbook and copy sheet2 to new workbook and rename the new Workbook and the sheet the data was copied to..

Click the "Go!" button in the attached Workbook...

Please Login or Register  to view this content.

I didn't get a Harvest Delivery value for:
Chilis : P9 Jalapeno (B 0.31 Ha) 0.31 30000

As in the sample you provided .. it did not exist...

Thanks Apo just what I wanted works perfectly.

I think this is what you want?

See attachment..

Apo, Thanks again mate. Just needed to reverse the first formula and worked perfectly. You are a legend.

No worries.. maybe next time i get up there we have a Bundy..

Anytime your up here mate just send us a message.