I have syntax error for macro in Excel

Question

I got syntax error for macro code in excel. Macro code below:

Sub concatnate()
'
' concatnate Macro
'
' Keyboard Shortcut: Ctrl+t
'
    ActiveCell.FormulaR1C1 = _
        "=CONCATENATE("","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","",""& _
        "","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","")"
    Range("C1").Select
    Selection.Copy
    Range("C3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Cells.Replace What:=",,", Replacement:="", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
End Sub

i made some changes in code then syntax error resolved but i did not get the exact result new is- — Sandeep Gupta
– Sandeep Gupta, Commented Sep 15, 2015 at 14:47

durbnpoisn · Accepted Answer · 2015-09-15 14:28:53Z

2

It's pretty simple, really.

You can't put double quotes INSIDE a set of double quotes.

You need to replace all those inner quotes with double-double quotes

ActiveCell.FormulaR1C1 = _
        "=CONCATENATE("""","""")"

As per comment, this answer has been corrected.

edited Sep 15, 2015 at 14:28

answered Sep 15, 2015 at 14:16

durbnpoisn

4,6792 gold badges19 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user4039065 Over a year ago

This is Excel VBA, not ASP Classic or T-SQL. The single quotes are not going to work as an alternative.

durbnpoisn Over a year ago

You, sir, are correct. So I tried a couple of different things. It seems that double-double quotes is the correct way to do this.

user4039065 · Accepted Answer · 2015-09-15 14:29:22Z

It seems that the VBE is getting a little confused trying to make sense of all those commas. It can be corrected as,

ActiveCell.FormulaR1C1 = _
    "=CONCATENATE("","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","",""," & _
                 ""","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","")"

If all you want is 99 commas then the following might be better.

ActiveCell.FormulaR1C1 = "=REPT(CHAR(44), 99)"

Collectives™ on Stack Overflow

I have syntax error for macro in Excel

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related