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, Feb 12 '18 at 16:40

score 4 · Accepted Answer · answered Feb 12 '18 at 16:34

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 '18 at 16:34

Scott Holtzman

27,099
5
37
72

1

ohhhhhhhhhhhhhhhhhh so thats it. thank you sooooo much and you solved like half my sheets problems with the application.enableevent = false. – ben mazor Feb 12 '18 at 16:39
+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 – Feb 12 '18 at 16:42
1

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`. – Feb 12 '18 at 20:48

score 1 · Answer 2 · answered Feb 12 '18 at 16:41

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

score 0 · Answer 3 · answered Feb 12 '18 at 16:19

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 '18 at 16:19

Vityata

42,633
8
55
100

Why remove the `.Value`?? – Tom Feb 12 '18 at 16:33
@tom - in this case it is a bit useless - https://stackoverflow.com/questions/32996772/is-value-actually-the-default-property-of-the-range-object – Vityata Feb 12 '18 at 16:37

score 0 · Answer 4 · answered Feb 12 '18 at 16:36

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

score 0 · Answer 5 · answered Feb 12 '18 at 16:36

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

VBA Replace() not working

5 Answers5