CODEWITHSUNDEEP
stringvbaexcel
user2812791
user2812791

Reputation: 71

Unable to concatenate more than two strings in excel vba

I wanted to concatenate three strings. Two of these are extracted from another sheet in the workbook using a variable pointer,x, while the third is the space (" "), separating them. The code is as follows:

Range("B1").Formula = "=CONCATENATE(Actuals!B" & x + 1 & "," ",Actuals!C" & x + 1 & ")"

However, I am getting a compiling error for this. But, when I tried without the space, it worked. The code is as follows:

Range("B1").Formula = "=CONCATENATE(Actuals!B" & x + 1 & ",Actuals!C" & x + 1 & ")"

As you can see, I have merely removed the space. Can someone please help me with this. Can I concatenate only two strings using VBA???

Thanks in advance.

Upvotes: 1

Views: 890

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149277

If you want to use " " as one of the parameters then you have to put them in double quotes.

The best way is to always record a macro if you are confused.

Is this what you are trying?

Range("B1").Formula = "=CONCATENATE(Actuals!B" & _
                      x + 1 & _
                      "," & _
                      """ """ & _
                      ",Actuals!C" & _
                      x + 1 & _
                      ")"

or this?

Range("B1").Formula = "=CONCATENATE(Actuals!B" & _
                      x + 1 & _
                      ","" """ & _
                      ",Actuals!C" & _
                      x + 1 & _
                      ")"

Upvotes: 1

Related Questions