VBA String Substitution question

See code below.  I am attempting to do a simple string substitution (replace an Asc() 225 with a space and an Asc() with a dash "-".  when the code is run, instead of looping thru for the length of the string, it just executes one time.... and then exits the function, and then returns again for the number of iterations of the string length??????

Any and all help is appreciated.  Here is an example string: Receiver,áDirecTVáBasicáDefinition

The return string from the function should be:  Receiver, DirectTV Basic Definiton


Function AddSpaceDash(LookIn As String) As String


Dim Ctr As Integer
Dim strHold As String
Dim codeHold As Integer
Dim lLength As Integer

lLength = Len(LookIn)

 For Ctr = 1 To lLength

  Select Case Asc(Mid(LookIn, Ctr, 1))
         
          Case 225
            Mid(strHold, Ctr, 1) = " "
         
          Case 161
            Mid(strHold, Ctr, 1) = "-"
       
          Case Else
            Mid(strHold, Ctr, 1) = Mid(LookIn, Ctr, 1)
           
  End Select
 
 Next


AddSpaceDash = strHold

End Function
Rick NorrisAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ThomasMcA2Commented:
Why not use the built-in replace function?

Replace(strInput, Chr(225), " ")

Open in new window

0
Martin LissOlder than dirtCommented:
No loop needed
Function AddSpaceDash(LookIn As String) As String

LookIn = Replace(LookIn, Chr(225), " ")
LookIn = Replace(LookIn, Chr(161), "-")
AddSpaceDash = LookIn
End Function

Open in new window


BTW your sample doesn't contain any chr(161) characters.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rick NorrisAuthor Commented:
Martin:  Very GOOD!!  Yes, I knew the example did not contain a Chr(161) character...  

IF you could....  can you possibly explain WHY the loop was only executing one time....  exiting.... and then coming back again until length of loop???  I think the code would have worked if not for that????

If not then still a an exceptional solution to my problem.
0
Martin LissOlder than dirtCommented:
Your code actually didn't do even one complete loop and the reason for that is that as logical as it looks you can't build a string using Mid (or Left or Right) as you tried to do with Mid(strHold, Ctr, 1) = Mid(LookIn, Ctr, 1) and so the left hand portion of that gave an "invalid procedure call" error and the sub quit. If you had error handling in the sub you might have caught that.

To correct the problem you could have done things like the following where the new character is sandwiched between the left and right portions. Note that someplace you'd need to do strHold = LookIn first.

          Case 225
              strHold = Left$(strHold, Ctr - 1) & " " & Mid$(strHold, Ctr + 1)

Open in new window

0
Martin LissOlder than dirtCommented:
See the above for the explanation, but in any case you're welcome and I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest youincluding these two new ones.
An Experts Exchange Shortcut for the Truly Lazy (for Apple OS X)
A Guide to Writing Understandable and Maintainable VBA Code
Marty - MVP 2009 to 2015, Experts-Exchange Top Expert Visual Basic Classic 2012 to 2014
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.