VBA Concatenate Counter With Text Constant

[barim]

I have this piece of code that is concatenating "X" with numbers from column C. I would like to replace this with constant text value "CA" and starting counter number 100.
So, as long as there is data in column C, in column A should be values like: CA100, CA101, etc.
Thanks.


Dim lrow1 As Long
Dim c1 As Range ' Define variables

lrow1 = ActiveSheet.Cells(Rows.Count, "C").End(xlUp).Row ' Find last row in column C
' loop through each row in column C and concatenate constant "X" with value found in column C
For Each c1 In ActiveSheet.Range("C2:C" & lrow1)
Columns(1).Range("A2:A" & lrow1).FormulaR1C1 = "=CONCATENATE(""X"",RC[2],""X"")"
Next c1

[mdmackillop]

For Each c1 In ActiveSheet.Range("C2:C" & lrow1).Cells
i = i + 1
Columns(1).Range("A" & c1.Row) = "CA" & Format(i, "000")
Next c1


or by formula, adjust as required
="CA" & TEXT(ROW()-1,"000")

[barim]

Thank you so much. It works great. :clap: