CODEWITHSUNDEEP
excelexcel-2013vba
xxxRxxx
xxxRxxx

Reputation: 367

How do I concatenate cell values and text together using Excel VBA?

I have a repetitive task I'd like to automate instead of using the =Concatenate function all the time. Here's my code so far:

Cells(2, 5).Value = Cells(2, 1).Value&" - "&Cells(2, 2).Value

Unfortunately this results in the "Compile error: Expected: end of statement" error, which highlights the " - ". How can I sandwich that text, " - ", between those two values?

Upvotes: 9

Views: 82998

Answers (3)

Truongpx
Truongpx

Reputation: 21

One suggestion for whom need:

Private Sub CommandButton1_Click()
Dim i As Long
Dim j As Long

Dim x As String

i = 1
j = Range("max").Value
x = Cells(i, 2)

For i = 2 To j

x = x & " - " & Cells(i, 2)

Next i

'MsgBox (x)

Range("d1").Value = x

i = 0

End Sub

Upvotes: 2

paul bica
paul bica

Reputation: 10715

Cells(2, 5).Value = Cells(2, 1).Value & " - " & Cells(2, 2).Value

Upvotes: 11

legendjr
legendjr

Reputation: 276

@Joshua provided an answer for you situation. Another solution that is more broad is one I've used before. See the UDF copied here.

Option Explicit
Function ConcatenateRow(rowRange As Range, joinString As String) As String
    Dim x As Variant, temp As String

    temp = ""
    For Each x In rowRange
        temp = temp & x & joinString
    Next

    ConcatenateRow = Left(temp, Len(temp) - Len(joinString))
End Function

Then in your excel file, just use this formula, selecting the range of cells to join, and giving it a string (in this case " - ") to put in between them.

Upvotes: 5

Related Questions