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

Question

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?

Answer 1

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

Answer 2

@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.