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]

Your program is working as designed ... except you've programmed the substring processing to overwrite clean_string on every iteration. So the last iteration is the one that updates clean_string last, and so is actually the only one that goes towards creating clean_string.

Maybe this would work better:

   clean_string = cleaner_string 
   For l = 0 To UBound(substring)
        clean_string = Replace(clean_string, substring(l), " ")
    Next l

 

[johnmerlino]

Your program is working as designed ... except you've programmed the substring processing to overwrite clean_string on every iteration. So the last iteration is the one that updates clean_string last, and so is actually the only one that goes towards creating clean_string.

Maybe this would work better:

   clean_string = cleaner_string 
   For l = 0 To UBound(substring)
        clean_string = Replace(clean_string, substring(l), " ")
    Next l

Still a problem:

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

Loop


   
End Sub

As soon as it finds a cell which does contain an iteem in the array, it copies the same result to all the cells thereafter.

For example:


Owner1 Owner1
BURDINES 1225 LLC BURDINES 1225 LLC
MIAMI-DADE COUNTY MIAMI-DADE COUNTY
RELATED GROUP OF FLORIDA & RELATED GROUP OF FLORIDA
BURDINES 1225 LLC RELATED GROUP OF FLORIDA
CITY OF MIAMI DEPT OF P & D RELATED GROUP OF FLORIDA
MIAMI-DADE COUNTY RELATED GROUP OF FLORIDA
FEC R R CO RELATED GROUP OF FLORIDA
BDG 200 SOUTH MIAMI AVE LLC RELATED GROUP OF FLORIDA
STATE OF FLORIDA DOT RELATED GROUP OF FLORIDA

 

[johnmerlino]

I moved this question to this forum:

http://stackoverflow.com/questions/6768404/vba-macro-copies-same-data-to-each-cell

I spent 2 days trying to figure out what's going on here. And I am not sure why this behaves as it does.

 

[GlennUK]

Have the responses in the other thread solved this for you?

 

[johnmerlino]

Have the responses in the other thread solved this for you?

no, I just don't understand. Someone provides solution, but it causes other effects.


Now I looked at this code with a fine tooth comb. Yet look at the results it produces:


abc def
abc SR def
abc def
abc def TR
abc SR def TR

to:


abc def
abc def
abc def
abc def TR
abc def TR


It should have removed the "TR" at the end of the last two cells but it didn't.

Here is the 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" 'begin loop of cells

   c.Offset(0, 1) = c

   For k = 0 To UBound(end_string) 'check current iteration
   
      cleaner_string = "" 'reset cleaner string so it doesnt hold value of last iteration

      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 'if we didnt require initial cleaning, then just assign the current iteration to clean_string
   Else
      clean_string = cleaner_string 'make sure we assign the cleaning to clean_string for additional cleaning
   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)

Loop



End Sub

 

[johnmerlino]

ok figured it out. I had to reinitialize the variable outside for loop, otherwise the cleaning won't be updated.

 

[johnmerlino]

The only other issue is given that I want to iterate the length of column a but there may be a variable number of empty cells within that length, is there a better way to handle the while loop without forcing the end user to add the string 'End Loop' to the bottom of column a?

thanks for response

 

[GlennUK]

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)

 

[Norie]

Just passing through, but just thought I'd mention InStrRev.

You could use that to find, and remove if needed, the last occurrence of your end strings.