• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

JEHalm

New Member
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])"
 
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
Code:
& between values to concatenate them.  The [code]&
operator does the same thing as CONCATENATE()[/code]:



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

Asa
 
Back
Top