Add space in VBA concatenation string Columns 3, 4, 5.space would be in column 3

[JEHalm]

Is there an easy way to insert a space between RC-[2], and [RC-1]?


I've been noodling it for a while with no success. I was thinking of "Concatenate (RC[-2], " ", [RC-1]" to no avail.


Dim xRow As Integer

xRow = Range("A65536").End(xlUp).Row

Range(Cells(2, "D"), Cells(xRow, "D")).FormulaR1C1 = _

"=CONCATENATE(RC[-2],INSERT SPACE HERE ,RC[-1])"

 

[asa]

I was thinking of "Concatenate (RC[-2], " ", [RC-1]" to no avail.



Looks like you may have a couple typos in what you indicated compared to what you actually tried?


Maybe you tried setting FormulaR1C1 to:

"=CONCATENATE(RC[-2]," ",RC[-1])"



If so, your only problem is in expressing the quotation marks in the string. VBA thinks the quote terminates the string after the first comma. It doesn't know you mean to literally include quotation marks IN your string.


To do so, double the literal quotes up:



"=CONCATENATE(RC[-2],"" "",RC[-1])"

You can also use

& between values to concatenate them.  The [code]&

operator does the same thing as CONCATENATE()[/code]:



"=RC[-2]&"" ""&RC[-1]"

Asa