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

detriez

Board Regular
Joined
Sep 13, 2011
Messages
151
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 can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,091
Office Version
  1. 2016
Platform
  1. Windows
you forgot a row number: With Range("O1:O" & lr)
 

detriez

Board Regular
Joined
Sep 13, 2011
Messages
151
Office Version
  1. 365
Platform
  1. Windows
Thanks for that Mart
No errors but, nothing happened
 

EXCEL MAX

Active Member
Joined
Nov 11, 2020
Messages
392
Office Version
  1. 2016
Platform
  1. Windows
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,883
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
 
Solution

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,981
Office Version
  1. 2016
Platform
  1. Windows
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,981
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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
 

detriez

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,883
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,981
Office Version
  1. 2016
Platform
  1. Windows
@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?
 

Watch MrExcel Video

Forum statistics

Threads
1,130,169
Messages
5,640,552
Members
417,151
Latest member
ChickenTenderer

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
Top