Tildes are for the range object's Replace method. It is not a RegEx method per se.
As for your match error, you need to post all code, not just a snippet. None of my examples ever caused a type mismatch. I use Option Explicit to make sure that types are declared. If you don't declare them, they are Variant.
You have been given the VBA methods to do what you need with both Regex and some Excel Range object Replace methods. You should visit some RegEx forums to get the proper pattern matching strings if you want to pursue that.
Try a new thread but state the goal with example before and after in an Excel file attachment and let the helpers decide what method will achieve the goal. You can reference this thread or your other one. I have responded to both threads and get confused which one we are discussing. Mark the two threads solved and the other when it is solved. Start a new thread for each problem. Those two problems are very related. Allow time for a solution to be posted. Most likely, a simple tweak like the pattern string is all that is needed for your next problem's solution.
