VBA to Search and Replace Text in Excel Cells Based on Conditions - Getting 'Type Mismatch' scenarios with Arrays
I need help solving I'm performance testing and I'm trying to create a VBA macro that searches through a specified range in an Excel worksheet and replaces certain text based on specific conditions. However, I'm working with a 'Type Mismatch' behavior when I try to use an array to hold my conditions. My code is designed to loop through each cell in the specified range and check if the cell's value meets certain criteria before performing the replacement. Here’s a snippet of my code: ```vba Sub ReplaceTextBasedOnConditions() Dim ws As Worksheet Dim cell As Range Dim conditions As Variant Dim i As Integer Set ws = ThisWorkbook.Sheets("Sheet1") conditions = Array("OldText1", "OldText2", "OldText3") For Each cell In ws.Range("A1:A100") For i = LBound(conditions) To UBound(conditions) If cell.Value = conditions(i) Then cell.Value = "NewText" End If Next i Next cell End Sub ``` When I run this macro, I get a 'Type Mismatch' behavior on the line where I declare the `conditions` array. I’ve made sure that the range A1:A100 contains text values, but I'm still not able to resolve this scenario. I've also tried explicitly declaring `conditions` as a `Variant`, but that didn't help either. Is there something wrong with how I'm using the array or how I'm checking the cell values? Any insights or alternative methods to achieve this would be greatly appreciated! This issue appeared after updating to Vba 3.10. I appreciate any insights! The stack includes Vba and several other technologies. Could this be a known issue?