vba code creating infinite loop?

**johnmerlino** · 07-18-2011, 04:42 PM

Hey all,

To me, it should not be creating infinite loop. It loops through outer array, checking if the end characters of a string match whats in the end_string array. Then for that string that was cleaned, it goes through another loop to remove any characters that match the second array. That is, if the index of second array is at all present in the cleaner string, then those characters should be removed from inner string. Then it outputs results in column B. Problem is when I run this macro, it causes excel to freeze and I have to force quit excel:

Sub StringChecker()

Dim string_arr() As Variant
Dim k As Integer

Dim c As Range
Set c = ActiveSheet.[A1]

end_string = Array(" &", _
            " TR", _
            " SR", _
            " DEFEN")
            
substring = Array(" JR ", _
            " SR ")

Do While c <> "End Loop"
            
   c.Offset(0, 1) = c
   
   For k = 0 To UBound(end_string)
   
      If Right(c, Len(end_string(k))) = end_string(k) Then
        cleaner_string = Mid(c, 1, Len(c) - Len(end_string(k)))
        
        For l = 0 To UBound(substring)

          clean_string = Replace(cleaner_string, end_string(l), "")
         

        Next l
         c.Offset(0, 1) = clean_string
      End If
       
   Next k
   
   

  
   Set c = c.Offset(1, 0)
Loop

End Sub

Force quitting excel is not the behavior I want. I was hoping it would convert this:

john smith JR & jennifer TR

to:

john smith & jennifer

thanks for response

**6StringJazzer** · 07-18-2011, 04:56 PM

Do you have the string "End Loop" somewhere in column A? It is probably better to look for a blank cell (like the code I posted to solve this problem for you in another thread), rather than an arbitrary string. You are guaranteed to hit a blank cell eventually.

**johnmerlino** · 07-18-2011, 05:09 PM

Originally Posted by 6StringJazzer

Do you have the string "End Loop" somewhere in column A? It is probably better to look for a blank cell (like the code I posted to solve this problem for you in another thread), rather than an arbitrary string. You are guaranteed to hit a blank cell eventually.

Problem with what you provided was if there was an empty cell in cell a, then the loop ends. But even if there is empty cell in cell a, the next one might not be empty, so I wouldnt want the loop to just end. but actually I did forget to put the string "End Loop" and now the loop does end, but the inner loop does not remove the string " JR " or " SR " if its in cell, even though the REPLACE function should be doing that.

Thanks for response

**6StringJazzer** · 07-18-2011, 08:58 PM

I don't understand how you are using the variable substring. You are using its array size for your inner For loop, but you never reference the array anywhere in your code.

I suspect that

' this line
clean_string = Replace(cleaner_string, end_string(l), "")

' should be changed to this
clean_string = Replace(cleaner_string, substring(l), "")

**johnmerlino** · 07-19-2011, 06:11 PM

Thanks for response.

I apply this:

Sub StringChecker()


Dim string_arr() As Variant
Dim k As Integer


Dim c As Range
Set c = ActiveSheet.[A1]


end_string = Array(" &", _
            " TR", _
            " SR", _
            " DEFEN")
            
substring = Array(" SR ", _
            " JR ")


Do While c <> "End Loop"
            
   c.Offset(0, 1) = c
   
   For k = 0 To UBound(end_string)
   
      If Right(c, Len(end_string(k))) = end_string(k) Then
          cleaner_string = Mid(c, 1, Len(c) - Len(end_string(k)))
          
        For l = 0 To UBound(substring)

          clean_string = Replace(cleaner_string, substring(l), " ")
         

        Next l
        
        c.Offset(0, 1) = clean_string
        
      End If
       
   Next k

   Set c = c.Offset(1, 0)


Loop


End Sub

And for the substring array, it only ever applies the last index. It's as if the indexes before the last get overwritten or something. For example, if JR is last index, then it will only remove JR from string. If SR is last index, then it will only remove SR from string.

Thanks for response.