Excel VBA to Find and Replace Text in Formulas - advanced patterns with Nested Functions
I'm updating my dependencies and I'm relatively new to this, so bear with me... I'm trying to use Excel VBA to find and replace a specific text string within formulas across multiple sheets in my workbook. The scenario arises when some formulas have nested functions, as my code seems to skip those or produce unexpected results. For instance, when I have a formula like `=SUM(IF(A1:A10="apple", B1:B10, 0))`, and I want to replace "apple" with "orange", it doesn't always work as intended. I've attempted to loop through all the worksheets and check each cell's formula, but when I reach those complex nested formulas, it either throws an behavior or doesn't make the replacement. Here's the code I currently have: ```vba Sub ReplaceTextInFormulas() Dim ws As Worksheet Dim cell As Range Dim oldText As String Dim newText As String oldText = "apple" newText = "orange" For Each ws In ThisWorkbook.Worksheets For Each cell In ws.UsedRange If cell.HasFormula Then If InStr(cell.Formula, oldText) > 0 Then cell.Formula = Replace(cell.Formula, oldText, newText) End If End If Next cell Next ws End Sub ``` When I run this code, it seems to skip cells with nested functions entirely or replaces the value incorrectly, sometimes leading to a `#VALUE!` behavior in the formulas. I've also checked for any potential issues with array formulas, but I need to pinpoint the question. Is there a better way to ensure that the replacements work correctly for all types of formulas, including those with nesting? Any insights on how to handle this situation would be greatly appreciated. Am I approaching this the right way? My development environment is Windows 10. Any ideas how to fix this?