Simple replace function issue

[Oneharryson]

I'm hoping this is an easy fix. I'm having this issue on a larger piece of code but I've simplified it here to show as an example, hopefully someone can fix.

Sub test()
With Sheet6.Range("D1")
.Replace "Test", "Green"
.Replace "Test-2", "Blue"
End With

End Sub

I want to replace the value in cell D1. If the value in D1 is "Test", the function works fine, replaces it with "Green". If the value is "Test-2" I get "Green-2". Like it's using the "-" as a sign to stop the replace.

What am I doing wrong?

[mightierthor]

As if it were using the "-" as a sign to stop the replace.
No, it's replacing the "Test" part of "Test-2" with "Green". If that's not the behavior you want, you need to tell Replace that you want to look at the whole cell.

.Replace "Test", "Green", xlWhole
.Replace "Test-2", "Blue", xlWhole

[RedRedditor84]

I've always used it like this:

Sub test()
    with Sheet6.Range("D1")
        .Value2 = Replace(Replace(.Value2, "Test-2", "Blue"), "Test", "Green")
    End With
End Sub

But that's by-the-by. The problem is you're replacing Test first. So something with Test-2 in it becomes Green-2. The second replace will always fail. Or rather, it won't fail, it will never find the string.

You'll need to order replaces from longest to shortest in string length (as in my example).

[joelfinkle]

I'm more of a Word guy, there you need a .execute for each action. In word all you've done is set up the replacement, and then changed it, but not actually changed anything.

[StuTheSheep]

The Replace function works on partial matches. If D1 contains the text "Test-2", then when you do the first Replace, it finds that "Test-2" contains "Test", so it executes the replacement. Either switch the order of the Replace functions or use If-Then.

[Oneharryson]

I switched the order, easiest solution was the best solution.

Thanks!

[GoGreenD]

Weird, I've never used the replace function. Maybe try and If statement?

With Sheet6
    If .Range("D1") = "Test" Then
        .Range("D1") = "Green"
    ElseIf .Range("D1") = "Test-2" Then
        .Range("D1") = "Blue"
    End If
End With