How to combine cell values ​​and text together using Excel VBA?

Question

I have a recurring task that I would like to automate, and not use the = Concatenate function all the time. Here is my code:

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

Unfortunately, this leads to the error "Compilation error: expected: end of statement", which emphasizes "-". How can I sandwich the text, “-”, between these two meanings?

Answer 1

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

Answer 2

One suggestion for anyone:

 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 

Answer 3

@ Joshua gave you the answer. Another wider solution is the one I used before. See 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, simply use this formula by selecting the range of cells to join and give it a line (in this case, “-”) to set between them.