Macro with If statements to find word and then copy

[ScabbyDog]

I need a macro to look for the first NUMBER in column C. The number will be ALONE in a cell always. When it finds this number I want it to look 2 cells to the left and if the word 'CON' appears then copy the number and paste it on the first unused row in Sheet2 column F BUT only if in column A in the row ABOVE where the first number is found equals what is contained in cell C1. If the word 'PON' appears instead of 'CON then copy the number and paste it on the first unused row in Sheet2 column H BUT only if in column A in the row ABOVE where the number is found equals what is contained in cell C1. If the word 'TRIP' appears instead of the other two words then copy the number and paste it on the first unused row in Sheet2 column X BUT only if in column A in the row ABOVE where the number is found equals what is contained in cell C1.

After it finds the first number it should loop and find the 2nd number and apply the same criteria above except that instead of ensuring cell C1 = the cell Ax where X is the row above where the 2nd number is found, it looks at Ay where y is 2 rows above where the 2nd number is found. So then when it looks for the 3rd number it'll look for the 3rd row above where the 3rd number is found etc etc.

I know this is possible somehow. Thanks in advance!

[ScabbyDog]

Sample.xlsm

Sample attached.

[ScabbyDog]

Hopefully I've worded my question so you guys can understand. If not, just let me know and I'll try and be clearer!

[ScabbyDog]

Sample.xlsm

I've updated the sample workbook. Sheet2 in the workbook attached now contains the results I expect from the macro running.

[ScabbyDog]

The below would solve the first part of my query if someone can help me out with the bits in bold

Please Login or Register  to view this content.

[tuongtu3]

Hi ScabbyDog,
I don't know VBA code but I use Formula so please try it and sorry if that's not what you want.
Hope expert will be helping you after this
Regards,
tt3

[skywriter]

Try this.

Please Login or Register  to view this content.

[ScabbyDog]

@Skywriter - Super stuff. Solves my query perfectly. Can't praise you enough!