Concatenate macro issues

[j3andc]

I have written the following code:

ActiveCell.Formula = "=CONCATENATE(RIGHT('Student Name'!D2,(LEN('Student Name'!D2) - FIND(" ",'Student Name'!D2))),", ",LEFT('Student Name'!D2,FIND(" ",'Student Name'!D2) - 1))"

I am getting a complile error: Expected:end of statement message.

I know this is referring to the FIND Function and the (" "). I don't understand why nor how to write it different. This formula works when written in a cell, but not when I try to use it in the macro.

I also know that if I remove the space, the message goes away for this, but returns on the second text string of the CONCATENATE function (", "). This time it is focusing on the comma. Again, I do not understand why.

As always, any assistance is greatly appreciated.

 

[VoG]

Try doubling-up the quotes

ActiveCell.Formula = "=CONCATENATE(RIGHT('Student Name'!D2,(LEN('Student Name'!D2) - FIND("" "",'Student Name'!D2))),"", "",LEFT('Student Name'!D2,FIND("" "",'Student Name'!D2) - 1))"

 

[j3andc]

It is typically something simple. That did the trick.

Thanks for you help.