Inner Loop not working correctly

[johnmerlino]

Hey all,

I would like to convert this:

abc SR abc &
abc JR abc TR

to this:

abc abc
abc abc

In this code:

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)))
        
      End If
      
    
   Next k
   For l = 0 To UBound(substring)
        clean_string = Replace(cleaner_string, substring(l), " ")
         
    Next l
        
    c.Offset(0, 1) = clean_string
    
   Set c = c.Offset(1, 0)

Loop

End Sub

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.

 

[GlennUK]

Thanks Norie ... how is Yaren?

 

[johnmerlino]

Loop each column by finding the last cell in the column. You'd then use a For Each loop to process each cell in the range.

For Each c in Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)

thanks

 

[johnmerlino]

I am still having a problem with this:

Sub StringChecker()
Dim string_arr() As Variant
Dim k As Integer
Dim c As Range
Set c = ActiveSheet.[A1]
 
end_string = Array(" &"," TRS"," TRUST", " JTRS")
substring = Array(" SR ", " II ", " LE ")
 
For Each c In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
   c.Offset(0, 1) = c
   cleaner_string = ""
      
   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)))
      End If
   Next k
   
   If cleaner_string = "" Then
      clean_string = c  
   Else
      clean_string = cleaner_string  
   End If
   
   
   For l = 0 To UBound(substring)
        clean_string = Replace(clean_string, substring(l), " ")
    Next l
    
    
    c.Offset(0, 1) = clean_string
   Set c = c.Offset(1, 0)
 
Next c
End Sub

For example this name:

abc a def TRUST &

will return this:

abc a def TRUST

even though it should return this:

abc a def

since both " &" and " TRUST" are in the end_string array. It seems to only do one pass by, even though it's clearly in a loop and should clean out all indices of that loop. Not sure what's going on.

Thanks for response.

 

[shg]

in this loop:

   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)))
      End If
   Next k

You are always operating on c, not the progressively-trimmed result. Step through the code.