TheRedCardinal
Board Regular
- Joined
- Jul 11, 2019
- Messages
- 243
- Office Version
- 365
- 2021
- Platform
- Windows
In one of my cells in my workbook I have a formula that determines the directory of the current file:
Its purpose is two fold - 1 is to be a visual reminder to the user to check they have saved the file in the right place; and 2 the string is used as a named range in my VBA code.
In my VBA code, the Sub opens up a CSV file in that folder, performs some functions (find and replace) on it, and then saves it, before closing and returning to the original workbook.
At this point, the formula above now shows "#Value" error.
I suppose it would be easy enough to write a line that puts the formula back in the cell, but I was wondering why this happens and is there a way to prevent it?
Excel Formula:
=LEFT(CELL("filename"),FIND("[",CELL("filename"),1)-1)Its purpose is two fold - 1 is to be a visual reminder to the user to check they have saved the file in the right place; and 2 the string is used as a named range in my VBA code.
In my VBA code, the Sub opens up a CSV file in that folder, performs some functions (find and replace) on it, and then saves it, before closing and returning to the original workbook.
At this point, the formula above now shows "#Value" error.
I suppose it would be easy enough to write a line that puts the formula back in the cell, but I was wondering why this happens and is there a way to prevent it?
