CODEWITHSUNDEEP
vbaexcel
Sandeep Gupta
Sandeep Gupta

Reputation: 11

I have syntax error for macro in Excel

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

Upvotes: 1

Views: 148

Answers (2)

user4039065
user4039065

Reputation:

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)"

Upvotes: 1

durbnpoisn
durbnpoisn

Reputation: 4669

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.

Upvotes: 2

Related Questions