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

detriez

Board Regular
Joined
Sep 13, 2011
Messages
193
Office Version
  1. 365
Platform
  1. Windows
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


Code:
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
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
you forgot a row number: With Range("O1:O" & lr)
 
Upvote 0
Thanks for that Mart
No errors but, nothing happened
 
Upvote 0
Try this code with correction...
VBA Code:
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
 
Upvote 0
How about
VBA Code:
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
 
Upvote 0
Solution
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.
 
Upvote 0
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...
VBA Code:
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
 
Upvote 0
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
 
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 0
@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?
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,243
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top