Replace characters with a Space Run time 1004 Method 'Range' of object'_Global failed

[detriez]

I am trying to get this to replace all instances of "-" or "/" with a space when column B is populated
This is throwing a run-time '1004': error Method 'Range' of object'_Global failed

Sub CleanPhone(control As IRibbonControl)

    Dim s As String


lr = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row + 1
    Application.ScreenUpdating = False
    
    With Range("O:O" & lr)
    
         s = lr
         junk = Array("(", ")", "-", "/")
    For Each a In junk
            s = Replace(s, a, " ")
        Next a
     End With
      
 Application.ScreenUpdating = True
 
End Sub

 

[mart37]

you forgot a row number: With Range("O1:O" & lr)

 

[detriez]

Thanks for that Mart
No errors but, nothing happened

 

[EXCEL MAX]

Try this code with correction...

Sub CleanPhone(control As IRibbonControl)

    Dim s As String
    Dim junk
    Dim vA, vA2()
    Dim vN As Long, a As Long, lr as Long, vN2 As Integer

    lr = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row
    With ActiveSheet
        vA = .Range("B1:B" & lr)
        ReDim Preserve vA2(1 To lr, 1 To 1)
        junk = Array("(", ")", "-", "/")
        For vN = 1 To lr
            s = vA(vN, 1)
            For vN2 = 1 To 4
                s = Replace(s, junk(vN2 - 1), " ")
            Next vN2
            vA2(vN, 1) = s
        Next
       .Range("B1").Resize(lr, 1) = vA2
     End With

End Sub

 

[Fluff]

How about

Sub CleanPhone(control As IRibbonControl)

    Dim i As Long


LR = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row + 1
    Application.ScreenUpdating = False
    
    With Range("O1:O" & LR)
      junk = Array("(", ")", "-", "/")
      For i = 0 To UBound(junk)
         .Replace junk(i), " ", xlPart, , , , False, False
        Next i
     End With
      
 Application.ScreenUpdating = True
 
End Sub

 

[Rick Rothstein]

You have a few problems in your code. The variable "lr" is a number which you assign to your string variable "s" and then proceed to use your loop to replace those unwanted characters in that number. Also, you have a With statement but never reference the object of that With statement. I think you wanted to use the With statement's range's Replace method on it but you used VB's Replace function instead of Excel's range's Replace method. Best if you start again. I see while I was typing this that you got two postings offering alternate code. I'll look and see how those codes are structured and come back with my own version if it would be significantly different.

 

[Rick Rothstein]

I think the other code offerings will allow some instances of leading and double spaces in their output. Here is my take on code to do what I think you want as a final outcome...

Sub CleanPhone()
  Dim LR As Long
  LR = Cells(Rows.Count, "B").End(xlUp).Row + 1
  Range("O1:O" & LR) = Evaluate("TRIM(TEXT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(O1:O" & LR & _
                       ",""("",""""),"")"",""""),""-"",""""),""/"",""""),"" "",""""),""# ### ### ####""))")
End Sub

 

[detriez]

Thanks all for you help! I got it working with Fluff's code

@Rick Rothstein .
I completely missed lr being a number. I thought if I didn't declare it somehow it wouldn't be relevant but, the function itself returns a numeric value. I see that now.
Looking at the help others have graciously posted... I could see I was all over the place.

Thanks all for taking the time to help

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Rick Rothstein]

@Fluff and detriez

As I mentioned in my last posting (Message #7), I believe Fluff's code can leave you with leading and internal double spaces within its output... is that what you want or do you need additional code to "Trim" them down?