Logo 
Search:

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: 708
  

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
("filename",A1))-SEARCH("]",CELL("filename",A1))))"
End Sub

Share: 

 

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 =
"=RIGHT(CELL(""filename"",A1),(LEN(CELL(""filename"",A1))-SEARCH(""]"",CELL(""fi\
lename"",A1))))"

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




Tagged:        

 
 
 
 

Related Post