fill selected cell with formula

Greasle

New Member
Joined
Jul 24, 2017
Messages
21
#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?
 
Another UDF to give the full path

Formula in cell
=GetFullPath()

Code:
Function GetFullPath() As String
    Application.Volatile
    GetFullPath = ActiveWorkbook.FullName
End Function


Again - must be placed in Standard module
Function should show (alongside built-in functions) as you begin to type = GetFull....

Note
The function refreshes whenever the workbook recalculates
 
Last edited:
Upvote 0

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Excel does not automatically recalculate when using SaveAs

Placing this in ThisWorbook module forces the workbook to recalculate when file is saved
- it refresh the value in the cell containing the UDF if the file has been saved with a different name

Code:
Private Sub Workbook_AfterSave(ByVal Success As Boolean)
    With Application
        .Calculation = xlManual
        .Calculation = xlCalculationAutomatic
    End With
End Sub
 
Upvote 0
full path including filename. Just the formula I posted.

If you enter the formula say in A1, try...

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

You need thus anchor the formula to a cell, preferably to the cell in which you implement it.
 
Upvote 0
Personal.xlsb makes the problem.

The module is in Personal.xlsb
if the function is in a module of a
book1.xlsx or xlsm there is no problem.

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

The sub however work fine with Personal.xlsb
 
Upvote 0
not a very nice solution.

The sub Yongle wrote works great for me within Personal.xlsb. Thanks again for that.

 
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top