No. of Row = No. of inputs

[Cheezee]

Hi guys, Hope you can help with my situation.
Here's the scenario I need to create a vba macro that can place a constant text in other column.
For example,
Excel.JPG
as you can see in the attachment, the no. of text("TC") proportional to the no. of Row in column A.
this column A and the no of Row will be user input. As long as the user input more row in Column A it will also generate a text("TC") every 3 row

Hoping for the best and thank you in advance

[TMS]

Option Explicit

Sub sInsertTCText()

Dim lLR As Long, i As Long
Application.ScreenUpdating = False
lLR = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To lLR
    Range("C" & i * 3).Value = "TC"
    Range("D" & i * 3).Value = "TC " & i
Next 'i
Application.ScreenUpdating = True
End Sub

Regards, TMS

[Cheezee]

Sir,TMS thank you very much..

Uhmmppp sir TMS question I'm new in VBA programming. I would like to know what the function of " Application.ScreenUpdating = False " and " Application.ScreenUpdating = True "

[bulina2k]

Hi there !

Application.ScreenUpdating = False
Turns screen updating off to speed up your macro code. You won't be able to see what the macro is doing, but it will run faster.

[TMS]

You're welcome


What bulina2k said


Regards, TMS


If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

[Cheezee]

Sir apo gonna try it, thank you

[apo]

Here's another for fun..

Private Sub CommandButton1_Click()
    Dim x, y
    x = Split(Replace(Join(Application.Transpose([A1].CurrentRegion.Columns(1)), vbCrLf), vbCrLf, Space(3)), Space(1))
    With [C3].Resize(UBound(x) + 1)
        .Value = Application.Transpose(x)
        y = .Replace("*", "TC")
    End With
End Sub

[bulina2k]

Here's another for fun..

Private Sub CommandButton1_Click()
    Dim x, y
    x = Split(Replace(Join(Application.Transpose([A1].CurrentRegion.Columns(1)), vbCrLf), vbCrLf, Space(3)), Space(1))
    With [C3].Resize(UBound(x) + 1)
        .Value = Application.Transpose(x)
        y = .Replace("*", "TC")
    End With
End Sub

for fun.. but for learning purposes also! very nice code!

[Cheezee]

Thank you sir bulina2k and TMS