loop thru sheets, find "Plastics", get figure 3 col. to right & label 10 Col righ put i

[queuesef]

I'm looking for assistance with a Search. The 1st tab is the 'Master' tab and is where I want to put the results of the Search from some 30+ worksheets. I need to pull the adjacent cells 4 columns to the right and 10 Columns to the right of the Search text and place these in the Master Tab. Then loop through the 2nd 3rd etc tab and put all the values in the Master tab. If the search text = "Plastics" in cell Q 30, then I would like to get the amount in cell T30 (4 columns to the right) and the text in cell Z30 (10 columns to the right) of the "Plastics". (I've been able to Loop through worksheets and separately find text, but I haven't been able to put the two together.)
I couldn't see the attachment, so it may have been uploaded 2 times?

[Richard Buttrey]

Hi

Is the information always in row 30 since that's not born out by your example?

[queuesef]

NO. (Is the information always in row 30 since that's not born out by your example? )
That is the part that varies from tab to tab. It is also the reason I'm using the SEARCH ("Plastics") to pull info from the adjacent cells. (I had to mock up data without Company info for the ATTACHMENT.)

[Richard Buttrey]

Assuming the answer to #2 is Yes then first list the sheet names in column A. This macro will do that for you

Please Login or Register  to view this content.

Then in B2 copied down

Please Login or Register  to view this content.

and similarly for C2

Please Login or Register  to view this content.

[sintek]

Hi queuesef

Wanna give this a go....

Please Login or Register  to view this content.

[Richard Buttrey]

NO. (Is the information always in row 30 since that's not born out by your example? )
That is the part that varies from tab to tab. It is also the reason I'm using the SEARCH ("Plastics") to pull info from the adjacent cells. (I had to mock up data without Company info for the ATTACHMENT.)

Are there multiple instances of 'plastic' on each sheet? This is not clear from your explanation so far since you've only mentioned looping in the context of looping through sheets not in cells withing a sheet.

This seems to be a much simplified example and probably doesn't represent the size of your actual data. We often find that when we give an answer to what is a trivial non representative example when the solution is used with the real world data it doesn't work because of factors like string length and other stuff which is important but hasn't been mentioned.

Therefore upload your real workbook (or at least a cut down copy), anonymised if necessary and manually add the results you expect to see. Clearly identify which is original data and which are the results and in a note clearly explain how you have arrived at your results.

[queuesef]

Sintek Nailed it. I'm having issues relating to the " If Not rng is Nothing then". I'm not accustomed to using or seeing, but I know it's taking the Negative of rng.
If the info was always in Row 30, then I would have liked Richard Buttrey's answer. Thank you so much both. Any other pointers for newbie? I try to build a framework (like a flow chart to help but I'm not so confident with all the different vba codes. I do know practice will definitely help.

[sintek]

Glad i could help...Good luck. thanks for rep +

[Richard Buttrey]

Sintek Nailed it. I'm having issues relating to the " If Not rng is Nothing then". I'm not accustomed to using or seeing, but I know it's taking the Negative of rng.
If the info was always in Row 30, then I would have liked Richard Buttrey's answer. Thank you so much both. Any other pointers for newbie? I try to build a framework (like a flow chart to help but I'm not so confident with all the different vba codes. I do know practice will definitely help.

Purely for info the Indirect functions I gave were NOT specific to row 30 They find the row where 'Plastic' exists wherever that may be.