VBA Replace() not working

Question

The command Replace() does not work for me. I finally got it working but only by skipping over error messages. Why a command works with when there are errors, dont ask, here is my working code. Can anyone please explain where im going wrong?

If Target.Address(0, 0) = "E3" Then
   Range("E3").Select
   On Error Resume Next
   Selection.NumberFormat = "@"
   Selection.Replace What:="-", Replacement:=""
   Selection.Replace What:=" ", Replacement:=""
   Selection = UCase(Selection.Value)
End If

Also, why wouldn't something like this work?

selection.value = replace(selection.value," ", "")

A bit of context for those who want: I'm using this to remove " - " and spaces out of product style numbers automatically. eg 05402-pt072 004 needs to equal 05402pt072004.

Thanks to anyone who responds.

If this is in a worksheet event such as Private Sub Worksheet_Change(ByVal Target As Range) remember to add Application.EnableEvents = False at the start and Application.EnableEvents = True at the end.... erm, as @ScottHoltzman had in his answer. — Darren Bartrup-Cook
– Darren Bartrup-Cook, Commented Feb 12, 2018 at 16:40

Scott Holtzman · Accepted Answer · 2018-02-12 16:34:33Z

4

Given the existence of Target I assume the code is in a Worksheet_Change event.

That said, the following should work:

If Target.Address(0, 0) = "E3" Then

   Application.EnableEvents = False 'stop change event from firing again
   With Target
       .NumberFormat = "@"
       .Replace What:="-", Replacement:=""
       .Replace What:=" ", Replacement:=""
       .Value = UCase$(.Value)
   End With
   Application.EnableEvents = True

End If

answered Feb 12, 2018 at 16:34

Scott Holtzman

27.3k5 gold badges42 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

ben mazor Over a year ago

ohhhhhhhhhhhhhhhhhh so thats it. thank you sooooo much and you solved like half my sheets problems with the application.enableevent = false.

user2261597 Over a year ago

+1 for skipping the nefarious On Error Resume Next Yes, there is use for it but ONLY if closely followed by On Error Goto 0

user4039065 Over a year ago

Given that both .Find and .Replace 'remember' whatever parameters were used last by the user on the worksheet, a more complete definition of the optional parameters should be specified. Specifically in this user's case, LookAt:=xlPart.

Davesexcel · Accepted Answer · 2018-02-12 16:41:38Z

1

I am assuming you are running into an infinite loop, as each time you update the targetcell, the worksheet_change event kicks in. How about

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim s As String
    If Target.Address = "$E$3" Then
        Application.EnableEvents = False
        s = Target
        s = Replace(s, " ", "")
        s = Replace(s, "-", "")
        Target = Format(s, "@")
        Application.EnableEvents = True
    End If
End Sub

answered Feb 12, 2018 at 16:41

Davesexcel

6,9922 gold badges31 silver badges44 bronze badges

Comments

Vityata · Accepted Answer · 2018-02-12 16:19:33Z

0

On a new Excel worksheet write this:

Sub TestMe()

    Range("A1") = "05402-pt072 004"
    Stop
    Range("A1").Value = Replace(Range("A1").Value, " ", "")
    Stop
    Range("A1").Value = Replace(Range("A1").Value, "-", "")

End Sub

Then press F5. The 05402-pt072 004 appears on A1
Press F5. Now the text on A1 is 05402-pt072004.
Press F5. Now the text on A1 is 05402pt072004.

Enjoy!

As a next step, you may remove the .Value from the code. Then try to write With Range("A1") etc.

answered Feb 12, 2018 at 16:19

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

2 Comments

Tom Over a year ago

Why remove the .Value??

Vityata Over a year ago

@tom - in this case it is a bit useless - stackoverflow.com/questions/32996772/…

Xabier · Accepted Answer · 2018-02-12 16:36:27Z

0

This will also work:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$E$3" Then
       Target.NumberFormat = "@"
       Value = Target.Value
       Value = Replace(Value, "-", "")
       Value = Replace(Value, " ", "")
       Target.Value = UCase(Value)
    End If
End Sub

answered Feb 12, 2018 at 16:36

Xabier

7,7251 gold badge11 silver badges21 bronze badges

Comments

Tom · Accepted Answer · 2018-02-12 16:36:30Z

0

Try replacing your code with the following

If Not Application.Intersect(Target, Me.Range("E3")) Is Nothing Then
    With Target
        .NumberFormat = "@"
        Replace expression:=.Value2, Find:="-", Replace:=vbNullString
        Replace expression:=.Value2, Find:=" ", Replace:=vbNullString
        .Value2 = UCase(.Value2)
    End With
End If

answered Feb 12, 2018 at 16:36

Tom

9,9184 gold badges34 silver badges51 bronze badges

Collectives™ on Stack Overflow

VBA Replace() not working

5 Answers 5

3 Comments

Comments

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

3 Comments

Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related