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

detriez

Board Regular
Joined
Sep 13, 2011
Messages
175
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

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,096
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
175
Office Version
  1. 365
Platform
  1. Windows
Thanks for that Mart
No errors but, nothing happened
 

EXCEL MAX

Well-known Member
Joined
Nov 11, 2020
Messages
580
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
73,207
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
37,881
Office Version
  1. 2019
  2. 2010
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
37,881
Office Version
  1. 2019
  2. 2010
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
175
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
73,207
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,881
Office Version
  1. 2019
  2. 2010
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?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,950
Messages
5,834,527
Members
430,294
Latest member
ValiantGames

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