Excel VBA Concatenate cell with loop

Question

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

DisplayName · Accepted Answer · 2018-10-26 11:14:18Z

1

add one line

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

answered Oct 26, 2018 at 11:14

DisplayName

13.4k2 gold badges13 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

PaulJ Over a year ago

So, that has concatenated the number in the cell above ? So, i double click a new number i.e 300001 in C3, and D3 reads TLD300000

DisplayName Over a year ago

well why don't you test it?

PaulJ Over a year ago

Ah, i have just amended the code to remove the last row -1. So it reads,Cells(lastrow, "D")= "TLD" & Cells(lastrow , "C"). and that has worked a treat! thank you so much.

DisplayName Over a year ago

You are welcome. If my answer solved your question then you may consider marking it as accepted. Thank you

PaulJ Over a year ago

Cells(lastrow, "D") = "TLD" & Cells(lastrow, "C")

Joshua44 · Accepted Answer · 2018-10-26 11:14:59Z

0

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

answered Oct 26, 2018 at 11:14

Joshua44

92 bronze badges

1 Comment

PaulJ Over a year ago

hi Joshua, how would i insert that into my existing code ?

Collectives™ on Stack Overflow

Excel VBA Concatenate cell with loop

2 Answers 2

5 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related