Find all words in column and based on other cells, paste into other sheet in certain range

Final-Sample.xlsm

Find attached sample sheet. I need a macro that looks for the words "Penalty", "Try", "Conversion", "Drop Goal" in Column B only and when found, if the cell two cells directly to the right equals what is in Cell C1 then copy the word that is found (either Penalty, Try, Conversion or Drop Goal) and paste it into 'Sheet3 After'. You'll see in 'Sheet3 After' that in YR2:ABS2 there are numbers from 1 to 80. It needs to paste the word in the last used row of a column in that range in the row that matches one cell directly to the left of where the initial word was found.

So if "Try" is found in B50 and D50 = C1 then copy "Try". Then look at the number directly left of the word "Try". If it is say 12, then paste the word "Try" into 'Sheet3 After' cell ZC3. Then, find the next instance of one of the four words, until all words have been found in column B. If there are two instances of 12 for example, then it needs to paste in the last used row, so the next time it'd paste into ZC4 for example. Hope that all makes sense. The sample sheet should help.

Originally Posted by JLGWhiz

I threw this together in a hurry, but it might work.

Please Login or Register  to view this content.

This works perfectly! However, if there is another list of numbers of 1 to 80 on row 2 before the YR2:ABS2 range, then how can I alter it so that it doesn't paste in the wrong range?

Set fn = sh2.Range("2:2").Find(t, , xlValues, xlWhole)

You would make this line range specific like

Please Login or Register  to view this content.

Regards, JLG

ScabbyDog ,
Try

Please Login or Register  to view this content.

Awesome guys. Thanks so much. Great help.