Need help to append data in existing cells(rows) of single column and suffix numbers in increasing order

[minhaj18]

Hi All,

Please help me to create a VB script(in excel 2010) for the below scenario:

Consider column A and there are 100 rows in that column with some existing data in each cell.

e.g. A1, A2, A3,... = apple, bat, cat,..

Now what I need is a script that would do this:

TC1_apple, TC2_bat, TC3_cat,... (that is append data in the format XX1, XX2, XX3 into each of succeeding cells in that column A)

(For input it should ask the number of total rows (100 in this case), the format of the data to be inserted (like TC_ or AB_ or XY_ etc and the column name where this data needs to be inserted (column A) )

Is there any way to do this ?

Your help will be greatly appreciated!!


Thanks,
Minhaj

 

[VBA Geek]

this perhaps?

Sub ciao()
Dim lrow As Long: lrow = Cells(Rows.Count, 1).End(xlUp).Row
Dim x As Long
For x = 1 To lrow
Cells(x, 1) = "TC" & x & "_" & Cells(x, 1)
Next
End Sub

 

[VBA Geek]

oh you mentioned in the succeeding cells... so it needs to begin from the end of the data set?


Sub ciao()
Dim x As Long
Dim lrow As Long: lrow = Cells(Rows.Count, 1).End(xlUp).Row


For x = 1 To lrow
Cells(x + lrow, 1) = "TC" & x & "_" & Cells(x, 1)
Next
End Sub

 

[minhaj18]

Yep succeeding, I meant top down in a column. The first cod works perfectly!.. How can we make it work for other columns other than column A?. Could you please explain your code briefly?

Thanks a lot!! You are awesome!!

 

[VBA Geek]

in Cells(x,1), 1 identifies the column number, so if you want it to work or column B you have to change it to 2, for column C to 3 and so on.... or you can say cells(x, "A"), cells(x, "B")

x is a variable that increments by 1 and goes through all the specified rows and modifies them

 

[minhaj18]

Thanks.. One last question : If the rows are not succeeding, I mean data is scattered in different rows of the same column ( like A1=apple then few rows are blank then on A5=bat, few more blank rows then A12 = cat etc.. i.e. if data exists in the row then apply the formula else don't make any updates ) can we apply the above script to apply the formula ??..

Thanks in advance!!

 

[VBA Geek]

You can use the below code to accomplish it

Sub mrExcelForum()


Dim W As Worksheet: Set W = ThisWorkbook.Sheets("Sheet6") ' change it to your desired sheet


On Error Resume Next
Dim myData As Range: Set myData = W.Range("A:A").SpecialCells(xlCellTypeConstants)


If Err.Number <> 0 Then
MsgBox "No Data in Column A!"
Exit Sub
End If


On Error GoTo 0


Dim aCell As Range
Dim x As Long: x = 0




For Each aCell In myData.Cells
x = x + 1
aCell = "TC" & x & "_" & aCell.Value
Next
End Sub