excelstarter1
Board Regular
- Joined
- Jul 20, 2017
- Messages
- 81
Hi guys,
I am stuck with a little problem. I want to run a macro which adds the iferror function to the selected cell. It does work with the following code, however I dont want to be bound to the "local" formula. E.g. when the macro is used within the organization in Spain or France, the formula should be used/displayed correctly. If a colleague in France opens my macro (in his French Excel Version), the formula does not work because Excel does not recognize the English iferror term.
(1) How do I get rid of the FormulaLocal part and tell Excel to insert the "correct" iferror formula, no matter which Excel version is used.
(2) Furthermore, I want to run a macro which checks a selected cell for an iferror formula. So basically if the selected cell starts with =iferror(...;"error") then I want to get rid of the "error") part at the end and replace it by "no value") for example. It is important that only formulas with iferror at the beginning are selected. The iferror formula should not be embedded in a longer formular, e.g. =sum(iferror(...).
Do you have an idea how to do that in VBA??
Thank you very much in advance!
Regards
I am stuck with a little problem. I want to run a macro which adds the iferror function to the selected cell. It does work with the following code, however I dont want to be bound to the "local" formula. E.g. when the macro is used within the organization in Spain or France, the formula should be used/displayed correctly. If a colleague in France opens my macro (in his French Excel Version), the formula does not work because Excel does not recognize the English iferror term.
Code:
selection.FormulaLocal = "=IFERROR(" & Right(selection.FormulaLocal, Len(selection.FormulaLocal) - 1) & ";" & Chr(34) & "error" & Chr(34) & ")"
(1) How do I get rid of the FormulaLocal part and tell Excel to insert the "correct" iferror formula, no matter which Excel version is used.
(2) Furthermore, I want to run a macro which checks a selected cell for an iferror formula. So basically if the selected cell starts with =iferror(...;"error") then I want to get rid of the "error") part at the end and replace it by "no value") for example. It is important that only formulas with iferror at the beginning are selected. The iferror formula should not be embedded in a longer formular, e.g. =sum(iferror(...).
Do you have an idea how to do that in VBA??
Thank you very much in advance!
Regards