Please help!

in column CY I have a list of names and there will never be an empty cell in the list but it will be of varying length from time to time.
I want the VBA to copy the formula from cell CP1 and place it in Column CZ next to each entry in column CY - it must not extend beyond the length of the number of entries in column CY. I then want to check the results of the formula and wherever the letters "Ct" appear I want to change it to "J".

my problem is determining how to make the formulas the same number as the number of entries in column CY.

Can anyone help.

I am using Excel 2007 and Windows 7