Macro: Search for text from a cell in another sheet and copy it in

**petesha** · 08-08-2011, 09:23 AM

I have a spreadsheet with two tabs.

In tab one is a column with file names. I would like to apply a macro which searches the column of information in Sheet 1 for terms from a range of data in the Sheet 2. If it finds the term, it then copies the data from Sheet 2 into Sheet 1.

E.g.

Sheet 1
A B C
MTC_CBM_146_16483.pdf

MTC_CBM_146_3177.pdf

MTC_CCO_146_2564.pdf

MTC_CCO_146_2565.pdf

MTC_CCO_146_2566.pdf

MTC_CCO_146_2567.pdf

MTC_CCO_146_3606.pdf

MTC_CCO_146_3607.pdf

MTC_CCO_146_3745.pdf

MTC_CCO_146_3746.pdf

MTC_CCO_146_3748.pdf

MTC_CCO_146_3751.pdf

MTC_DCP_146_15848.pdf

MTC_DCP_146_17314.pdf

MTC_DCP_146_1798.pdf

MTC_DCP_146_2192.pdf

MTC_DCP_146_2453.pdf

MTC_DCP_146_2457.pdf

MTC_DCP_146_2458.pdf

MTC_DCP_146_2460.pdf

MTC_DCP_146_2467.pdf

MTC_DCP_146_2468.pdf

Sheet 2

A B
CBM Composition of a Sample of Bituminous Material
CBR Equivalent California Bearing Ratio
CCO Concrete Core Logs
CM_ Constituent Material in Recycled Aggregate
CMS Cover Meter Surveys
COC Certificate of Conformity
CPR Concrete Pour
CUB Concrete Cubes
DCP Dynamic Cone Penetration

So the macro looks at the list in sheet 1 and will then look at the data in sheet 2 bringing in the appropriate data from columns A and B so that the table once completed would look like:

A B C
MTC_CBM_146_16483.pdf CBM Composition of a Sample of Bituminous Material
MTC_CBM_146_3177.pdf CBM Composition of a Sample of Bituminous Material
MTC_CCO_146_2564.pdf CCO Concrete Core Logs
MTC_CCO_146_2565.pdf CCO Concrete Core Logs
MTC_DCP_146_15848.pdf DCP Dynamic Cone Penetration
MTC_DCP_146_17314.pdf DCP Dynamic Cone Penetration
MTC_DCP_146_1798.pdf DCP Dynamic Cone Penetration

I had started with the following code but don’t know how to carry out the functions I need!

Please Login or Register  to view this content.

Thank you for any help!

**petesha** · 08-08-2011, 09:36 AM

The A, B and C references above the data sets are in reference to columns but the tabs and spaces seem to disappear and I'm not sure how to leave them on to make this a bit clearer sorry!

**petesha** · 08-08-2011, 09:41 AM

In fact this is the spreadsheet, might be easier!

**petesha** · 08-09-2011, 04:19 AM

Anyone? Please?

**Miraun** · 08-09-2011, 09:36 AM

In the example that you posted, a majority of the files were MTC_***_###_####.pdf formatted titles.

The formula =VLOOKUP(MID(A13,5,3),'Test Types'!$A$2:$B$67,2,FALSE)
is accurate for any of the PDF names in that format (which is 226/247 of the example).

If you're looking for a VBA based solution though;
Excel has a hard time doing variable strings within a given string. If you're looking for a VBA solution, I would recommend starting with the Test Types Sheet, taking the values Column A, then storing the description from column B, then running a find/findnext on Materials Tests, posting the value there, with a loop on the Test Types sheet, rather than starting on the Material Testing page.

Unfortunately, I am a bit busy today and can't write the code itself for you at this time, but with that little snippet you provided, I'm sure you can figure it out with that way of thinking in mind.

**petesha** · 08-09-2011, 10:34 AM

Hi Miraun,

Thank you for responding. Unfortunately I may have given some sort of illusion that I know what I’m talking about in terms of these codes but I really don’t. Although it all seems logical I just don’t know the correct terms and things to make the codes work. What I posted was as a result of a lot of searching and I got it to work for one term for Sheet 2 and transpose the data into Sheet 1.

I didn’t even know I had asked something that was time consuming and if so I’m sorry about that. If is a matter of typing in a lot of iterations then I’m more than happy to do that, it’s just the bits of code at the start and the end which I have no idea about.

I will need to add this code to about 250 spreadsheets with about 30k rows in total so the MTC reference is only applicable in this spreadsheet sorry.

Thank you for coming back to me though!