Consulting

Results 1 to 3 of 3

Thread: VBA Concatenate Counter With Text Constant

  1. #1
    VBAX Regular
    Joined
    Jan 2016
    Posts
    55
    Location

    VBA Concatenate Counter With Text Constant

    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

  2. #2
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    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")
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  3. #3
    VBAX Regular
    Joined
    Jan 2016
    Posts
    55
    Location
    Thank you so much. It works great.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •