I am trying to mass-edit formulae using the Find and Replace feature, however, Excel keeps popping up and telling me the formula is not correct (which I know as I need to edit the front end and the back end of each formula).
Screen Shot 2018-08-06 at 11.17.48.png
Can somebody tell me how to turn this off as it keeps reversing the changes I make. I need to edit both sides of 100 formulae without changing the middle part and this is causing me untold grief.
Thanks in advance.
Last edited by doubleuson; 08-06-2018 at 07:41 AM. Reason: Solve Change
You have made a mistake editing your formula. What changes are you trying to make?
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
You can't force Excel to do something it's not going to be able to do (that's why it has error messages - it's saying to you, "You can't do this in Excel."). You will need to find a different way. What EXACTLY are you trying to do?
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
Use Excel Vba to change your formula as text.
Then do find and replace which won't throw any error since the formula's are resides as text.
After completing the process re-instate those text (formula's) showing as text to Real formula.
If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
Mark your thread as Solved
If the suggestion helps you, then Click *below to Add Reputation
Originally Posted by Glenn Kennedy
Screen Shot 2018-08-06 at 11.31.23.png
I am trying to bulk edit an IF function to test whether the value in cell D is 0, to remove the #DIV/0! error in multiple formulae at once. Everytime I try to change the front end, it says I have a missing parentheses at the end and undoes my changes. So then I try to add a parentheses to the end and it won't let me because incorrect formula.
I am not about to go through each one of them and painstakingly make those changes.
See, that's a decent suggestion. How would I go about doing it in VBA?
Nor should you have to, however you can't do what you want with Find & Replace for the reasons already explained, whether you like it or not. Maybe go with the VBA suggestion.I am not about to go through each one of them and painstakingly make those changes.
Using the replace dialog, replace = with something that won't be part of the formula (e.g. a pipe | character) so that the formula is not a real formula. Then make your changes as needed, then replace | with = to make them actual formulas again.
I've taken the liberty of giving your thread a decent title and moving it to the VBA section, as we now know that is what you are going to need to solve this.
How to install your new code
- Copy the Excel VBA code
- Select the workbook in which you want to store the Excel VBA code
- Press Alt+F11 to open the Visual Basic Editor
- Choose Insert > Module
- Edit > Paste the macro into the module that appeared
- Close the VBEditor
- Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)
Please Login or Register to view this content.
/SOLVED
Thanks for all your help.
By all means have a look and a crack at any of my (many) other issues. Life is hard as an Excel novice:
https://www.excelforum.com/excel-pro...ith-macro.html
https://www.excelforum.com/excel-for...istance-2.html
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks