Make several replacements at once (array)

[Truiz]

Good day fellas,

I was trying to use this

Sub Macro1()Dim Arrreplc As Variant
'Making Replacements all at once


Arrreplc = Array("(", ")", " ", "-")


 With Range("D2:D9")
     .Replace What:=Arrreplc, Replacement:="", LookAt:=xlPart, _
     SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
     ReplaceFormat:=False
     .NumberFormat = "[<=9999999]###-####;(###) ###-####"
 End With
End Sub

To create an array of the things I want to replace in the string but its actually not working... could you give me a hand

Thanks

 

[Rick Rothstein]

You cannot feed an array to the Replace method... it is nothing more than the code equivalent of Excel's Replace dialog box which only excepts single text strings in its "Find what" and "Replace with" fields. Give this macro a try...

Sub PhoneNumberToDigitsOnly()
  Dim Symbol As Variant
  With Range("D2:D9")
    For Each Symbol In Array("(", ")", " ", "-")
      .Replace Symbol, "", xlPart, , , , False, False
    Next
  End With
End Sub

 

[Truiz]

Thanks that worked great