View Full Version : [SOLVED] VBA Concatenate Counter With Text Constant

05-23-2017, 10:38 AM
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.

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

05-23-2017, 11:58 AM
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")

05-23-2017, 02:00 PM
Thank you so much. It works great. :clap: