barim
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.
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
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