VBA Excel Replace not always working

Question

OK this is really bugging me now.

Eventually I want to add some ifs and else's to this code but for now I simply have the below code.

' resize string if nessuary  
sname = Replace(arr(x), "ASR Port Provisioning General Questions", "General Questions")  
sname = Replace(arr(x), "ASR Port Provisioning", "ASR PP")  
sname = Replace(arr(x), "ASR Standard Network Device Config Changes", "Std Config Change")  
If Len(sname) > 30 Then sname = Right(sname, 15)

arr() has the elements

"ASR Port Provisioning General Questions", 
"ASR Port Provisioning",
"ASR Standard Network Device Config Changes"

Now for the "ASR Standard Network Device Config Changes" it works and gets shortened to "std Config Change"

But for the other two they get skiped and not replaced?

Now i know they contain the corrent info, I have even tried outputing arr(x) to form the seach string, but it still does not happen :)

OK seems it needed the tidy up, once wraped in if and elses

If InStr(arr(x), "ASR Port") Then

    If InStr(arr(x), "ASR Port Provisioning General Questions") Then
    sname = "General Questions"
    Else
    sname = Replace(arr(x), "ASR Port Provisioning", "ASR PP")
    End If

    Else
    sname = Replace(arr(x), "ASR Standard Network Device Config Changes", "Std Config Change")
End If

Work fine!! any one knwo whey three replace statments ogather stop each other working ?

Thomas · Accepted Answer · 2011-05-05 16:47:59Z

1

In your first code block, the problem is that you are resetting the value of sname on each call and thus only the last one "sticks". You either need another array to store all the replaced values, or you could update the existing array, or you could do your work on each item after you do the replace. We would need to know more about what you are doing with the data.

In the second code block, ASR Port must not be in the string arr(x) and thus it only executes the Else statement.

I'm guessing you are trying to do something like:

If InStr(arr(x), "ASR Port Provisioning General Questions") Then
    sname = Replace(arr(x), "ASR Port Provisioning General Questions", "General Questions")  

ElseIf InStr(arr(x), "ASR Port Provisioning") Then
    sname = Replace(arr(x), "ASR Port Provisioning", "ASR PP")  

Else
    sname = Replace(arr(x), "ASR Standard Network Device Config Changes", "Std Config Change") 

End If

answered May 5, 2011 at 16:47

Thomas

64.8k12 gold badges98 silver badges142 bronze badges

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

2 Comments

DevilWAH Over a year ago

no sorry the second code block works fine. But thank you for the first comment about resetting the value, I had assumed that it would try each replace in turn, if one worked, then that updated value would be tested aginst in the next string. I have jsut thought the other way to do it would be to use arr(x) in the first replace statment, and then sname in each aditinal one!!

DevilWAH Over a year ago

Now it all makes sence I was jsut over looking the string i was searcing!! DOH!!!!! Goes away feeling silly ;) LOL taht taken me ages to spot!!!

Collectives™ on Stack Overflow

VBA Excel Replace not always working

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related