CODEWITHSUNDEEP
excelvbafor-loopconcatenation
PaulJ
PaulJ

Reputation: 21

Excel VBA Concatenate cell with loop

My code below generates the next number in sequence from the cell above it, in the last blank cell of column C when it is double clicked.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As 
Boolean)
Dim lastrow As Long
lastrow = Cells(Cells.Rows.Count, "C").End(xlUp).Row + 1
If Not Intersect(Target, Range("C:C")) Is Nothing Then
If Target.Address = Cells(lastrow, "C").Address Then
Application.EnableEvents = False
Cancel = True
Cells(lastrow, "C") = Cells(lastrow - 1, "C") + 1
Application.EnableEvents = True
End If
End If
End Sub

What i would like to do is include a simple concatenate for the cell on the same row in column D, that prefixes the newly generated number with the letters "TLD". I have tried a couple of examples off this site, but i'm not having much success as i do not quite know how to include it within this code, or indeed if it should be?

So, for example, Cell C2 = 300000 Cell D2 should then read TLD300000. I don't want to use a formula as i will not know how many rows will be used over time. Any ideas? Thanks Paul

Upvotes: 0

Views: 600

Answers (2)

Joshua44
Joshua44

Reputation: 9

Try the following

Dim c As Range
For Each c In Range("C2:C" & Lastrow)
If c.Value <> "" Then
c.Value = c.Value & "TLD"
End If
Next c

Upvotes: 0

DisplayName
DisplayName

Reputation: 13386

add one line

        ….
        Cells(lastrow, "C") = Cells(lastrow - 1, "C") + 1
        Cells(lastrow, "D") = "TLD" & Cells(lastrow - 1, "C") '<--- added line

Upvotes: 1

Related Questions