fill selected cell with formula

[Greasle]

#Newbie
Hi all
I tried to write a sub or a function to populate a selected cell with the formular below:

=SUBSTITUTE(LEFT(CELL("filename"), FIND("]", CELL("filename")) - 1), "[", "")

But i fail. Used examples but no luck.

Beware the formula works only with a saved workbook

anyone?

 

[Yongle]

Hi

Sub TestThis()
    ActiveSheet.Range("A1").Formula = "=SUBSTITUTE(LEFT(CELL(""filename""), FIND(""]"", CELL(""filename"")) - 1), ""["", """")"
End Sub

 

[Yongle]

or as a UDF

place this formula in any cell
=FilePath()

place in Standard module

Function FilePath()
    Application.Volatile
    FilePath = Evaluate("=SUBSTITUTE(LEFT(CELL(""filename""), FIND(""]"", CELL(""filename"")) - 1), ""["", """")")
End Function

 

[Greasle]

Hi

Sub TestThis()
    ActiveSheet.Range("A1").Formula = "=SUBSTITUTE(LEFT(CELL(""filename""), FIND(""]"", CELL(""filename"")) - 1), ""["", """")"
End Sub

Thank you Yongle,

That set me on the road to make :

ActiveSheet.Range(Selection.Address).Formula = "=SUBSTITUTE(LEFT(CELL(""filename""), FIND(""]"", CELL(""filename"")) - 1), ""["", """")"

works.

 

[Greasle]

or as a UDF

place this formula in any cell
=FilePath()

place in Standard module

Function FilePath()
    Application.Volatile
    FilePath = Evaluate("=SUBSTITUTE(LEFT(CELL(""filename""), FIND(""]"", CELL(""filename"")) - 1), ""["", """")")
End Function

very happy with the Sub.

but the function results in #NAME ?

 

[Yongle]

"Beware the formula works only with a saved workbook"

Did you take your own advice?

 

[Aladin Akyurek]

@ Greasle

What are you trying to obtain? The full path or just the filename or just the sheet name?

 

[Greasle]

"Beware the formula works only with a saved workbook"

Did you take your own advice?

Yes.
The sub works.
but the function results in "#NAME?" on the same workbook.

 

[Greasle]

@ Greasle

What are you trying to obtain? The full path or just the filename or just the sheet name?

full path including filename. Just the formula I posted.

 

[Yongle]

but the function results in "#NAME?" on the same workbook.

Where did you paste the function?
- It must be in a standard module (not Sheet module, not ThisWorkbook module)