MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Compile error for new bit of code

  Asked By: Sunil    Date: Jan 18    Category: MS Office    Views: 896

My boss developed this formula for Excel. I want to automate placement
of code in any workbook, so I put it in my personal macro workbook.

However, the complie check expects an end of statement for the two
formula lines, after the second quotation mark. How do I get it to
accept that there will be many quotation marks?

Sub Date_File_Sheet_Name()
' Date_File_Sheet_Name Routine
' Enters current date, path, Workbook name and current sheet name in
the active cell.
' Moves one cell down, and enters the current sheet name.

ActiveCell.FormulaR1C1 = "=CONCATENATE(TEXT(NOW(),"dd mmmm
yyyy"), ", ", CELL("filename"))"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=RIGHT(CELL("filename",A1),(LEN(CELL
End Sub



3 Answers Found

Answer #1    Answered By: Alessio Smith     Answered On: Jan 18

For the first one try:
ActiveCell.FormulaR1C1 = "=CONCATENATE(TEXT(NOW(),""dd mmmm yyyy""),
"", "", CELL(""filename""))"

for the second use:
ActiveCell.Formula =

It doesn't matter that you use 'FormulaR1C1' (rather than just
'Formula') in the first as you don't have any cell  references, but it
does matter in the second.

Answer #2    Answered By: Sammy Anderson     Answered On: Jan 18

Double quotation marks. I never knew...

Answer #3    Answered By: Sammy Brown     Answered On: Jan 18

Try this:

Sub Date_File_Sheet_Name()
' Date_File_Sheet_Name Routine
' Enters current  date, path, Workbook name and current sheet  name in the active
' Moves one cell  down, and enters the current sheet name.
ActiveCell.FormulaR1C1 = "=CONCATENATE(TEXT(NOW(),""dd mmmm yyyy""), "", "",
ActiveCell.Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = "=Right(R[-1]C,Len(R[-1]C)-Find(""]"",R[-1]C))"
End Sub

Didn't find what you were looking for? Find more on Compile error for new bit of code Or get search suggestion and latest updates.