Replace Function on VBA

Question

I have this code,

Dim str As String, Dim replaceStr As String, Dim counter as Integer
str = "I have the number 3"

For counter = 1 To 5
    replaceStr = Replace(str, counter, 99)
Next counter

I want the replace function to catch when the counter = 3 so that it replaces the 3 with 99. So far I am still getting "I have the number 3".

I have tried Replace(str, CStr(counter), 99) and still gives me the same results.

If you check the value of replaceStr AFTER the for loop completes you will have I have the number 3 because the LAST iteration of the FOR loop was number "5". Instead, toss debug.print replaceStr INSIDE your FOR loop and rerun. You'll see 5 lines printed to your immediate pane and the third will properly read I have the number 99 as you expect. — JNevill
– JNevill, Commented Jun 21, 2018 at 19:11

John Coleman · Accepted Answer · 2018-06-21 19:11:42Z

4

You are doing the replacement when counter equals 3 but then undoing the replacement in the next pass through the loop. If you change your code to:

Sub test()
Dim str As String, replaceStr As String, counter As Integer
str = "I have the number 3"

For counter = 1 To 5
    replaceStr = Replace(str, counter, 99)
    Debug.Print replaceStr
Next counter
End Sub

You will see the output:

I have the number 3
I have the number 3
I have the number 99
I have the number 3
I have the number 3

Perhaps you can add the line If replaceStr <> str Then Exit For immediately after the Replace

answered Jun 21, 2018 at 19:11

John Coleman

52.1k7 gold badges59 silver badges127 bronze badges

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

1 Comment

QHarr Over a year ago

And to you for a far better explanation.

QHarr · Accepted Answer · 2018-06-21 19:18:53Z

1

You can apply the change to the actual string and not keep bringing in a "fresh" str.

Option Explicit
Sub test()
    Dim str As String, replaceStr As String, counter As Long

    str = "I have the number 3"

    For counter = 1 To 5
           str = Replace(str, counter, 99)
    Next counter

    Debug.Print str
End Sub

edited Jun 21, 2018 at 19:18

answered Jun 21, 2018 at 19:15

QHarr

84.5k14 gold badges58 silver badges105 bronze badges

4 Comments

John Coleman Over a year ago

This would work for OP's stated problem but might behave unexpectedly if For counter = 1 To 5 were changed to e.g. For counter = 1 To 9

QHarr Over a year ago

@JohnColeman Very true. I have swopped it for directly applying change to the str variable.

QHarr Over a year ago

Although, this might not be what is wanted!

John Coleman Over a year ago

But maybe it is (so +1) -- one purpose of Stack Overflow is to give ideas which can help programmers, not to give them complete solutions.

Collectives™ on Stack Overflow

Replace Function on VBA

2 Answers 2

1 Comment

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related