On Wednesday, October 22, 2014 8:36:47 AM UTC-5, Brian wrote:
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


LRow = Cells(Rows.Count, "CY").End(xlUp).Row
with Range("CZ1:CZ" & LRow)
.Formula = "Your formula"
.replace, "ct","j"
end with