0

I wrote a macro that would loop through a string and trim out all non-numeric characters. I ran into an issue where the last loop cycle would pass a null string to if not isnumeric() resulting in true, causing the string to attempt to replace that character.

Here is what the code accomplishes when inserted to a range loop:

Here is the original code:

    L = Len(s)
    i = 0
    While i <= L
        i = i + 1
        If Not (IsNumeric(mid(s, i, 1))) Then
            s = Replace(s, mid(s, i, 1), "")
            i = i - 1
            L = Len(s)
        End If
    Wend

You'll notice that whenever I delete a character from the string, I reduce the i counter by one. This is because if there are multiple non-numeric characters in a row, and I delete character #2, char #3 becomes the new char #2. So I needed the loop to go back and check that index number over again until it's a number, or the string is finished.

Well that created a problem: I ended up with an infinite loop because the last iteration passes a null string into that statement, and it keeps running the i-1 This problem persisted with both a while i <= L and with a for i = 1 to L

I found that I can include the conditional i <= L in the If statement, and it would then prevent the last iteration from reaching that point and causing an infinite loop. The working code is below:

    L = Len(s)
    i = 0
    While i <= L
        i = i + 1
        If Not (IsNumeric(mid(s, i, 1))) And i <= L Then
            s = Replace(s, mid(s, i, 1), "")
            i = i - 1
            L = Len(s)
        End If
    Wend

Can anyone explain why my For or While Loops ended up looping once again with an i greater than my L?

BrianH
  • 82
  • 9
  • Any chance you can share some sample inputs along with expected outputs? May help to provide some context for those that are more visual... like me :) – urdearboy May 19 '20 at 17:39
  • 3
    Why not loop backwards? Or avoid a loop and use regular expressions - you could copy the code online. – SJR May 19 '20 at 17:39
  • you are adding 1 to `i` so when `i = L` it is creating a mid of the charcters past the length of the string. Change `While i <= L` to `While i < L` – Scott Craner May 19 '20 at 17:39
  • 4
    This solution has 3 methods including regex which appears to be the fastest. Someone also shared a excel formula on this same post to accomplish this without VBA :0 https://stackoverflow.com/a/38797051/6706419 – urdearboy May 19 '20 at 17:41

0 Answers0