Do Until Endless Loop

[reberryjr]

I have some code where I'm evaluating the value of the last cell in column CB. If that value <> "Current", then that row is copied and pasted into the row below it. I had code that works fine for that, but it was only performing on one row at a time. I did some googling and it seems as though Do Until and Loop would be the answer. I put this code together based on some examples I found, but the code just continues to run, with no results.

Can someone help identify what I need to change?

Private Sub Worksheet_Activate()

Application.ScreenUpdating = False


Dim csws7 As Worksheet
Dim csLastRow7 As Long


Set csws7 = ThisWorkbook.Sheets("Financials")
csLastRow7 = csws7.Range("D" & Rows.Count).End(xlUp).Row


'If the last value in column CB doesn't = Current, then copy the last row and paste _
it to the next row.  Repeat this process until the last value in column CB = Current.
Do Until csws7.Range("CB" & csLastRow7).Value = "Current"


If Not csws7.Range("CB" & csLastRow7).Value = "Current" Then
    csws7.Range("A" & csLastRow7 & ":CB" & csLastRow7).Copy
    csws7.Range("A" & csLastRow7 + 1).PasteSpecial
    csws7.Range("X" & csLastRow7 + 1).Value = ""
    csws7.Range("AH" & csLastRow7 + 1).Value = ""
    csws7.Range("AR" & csLastRow7 + 1).Value = ""
    csws7.Range("BB" & csLastRow7 + 1).Value = ""
    csws7.Range("BL" & csLastRow7 + 1).Value = ""
    csws7.Range("BT" & csLastRow7 + 1).Value = 0
    csws7.Range("BU" & csLastRow7 + 1).Value = 0
    csws7.Range("BV" & csLastRow7 + 1).Value = 0
    csws7.Range("BW" & csLastRow7 + 1).Value = 0
    csws7.Range("BX" & csLastRow7 + 1).Value = 0
End If


Application.CutCopyMode = False


Loop


Application.ScreenUpdating = True


End Sub

 

[HackSlash]

I'm not clear on what you are trying to do. You say that you are evaluating the last row but then you complain that it only looks at one row at a time. The last row is indeed only one row. In fact, your code reflects this. Your code currently looks at one cell forever. This loop would only end if the value on that row magically changed to "Current" then your updates would run and the loop would exit.

Please explain, from the beginning, what you are actually attempting to accomplish here. How does the last row change? Are humans editing this document? Is a macro running? Neither human edits or other macro code can run while your code is running.

 

[reberryjr]

My comment of "...performing on only one row at a time." is better described as, only 1 row was being copied and pasted. Which, as you indicate, is what the code was supposed to do. I want the copy and paste performance to continue until the last used cell in column CB = "Current".

Since this is running when the worksheet activates, I could probably just code so that tabs activate and deactivate until the condition is met, but that wouldn't seem to be efficient.

 

[reberryjr]

Well, I added the code in red font, and I'm getting the results I want. I'm still not sold that this is the best way to go though.

Private Sub Worksheet_Activate()

Application.ScreenUpdating = False


Dim [COLOR=#ff0000]csws6[/COLOR], csws7 As Worksheet
Dim csLastRow7 As Long


[COLOR=#ff0000]Set csws6 = ThisWorkbook.Sheets("Pymt Tracker")[/COLOR]
Set csws7 = ThisWorkbook.Sheets("Financials")
csLastRow7 = csws7.Range("D" & Rows.Count).End(xlUp).Row


'If the last value in column CB doesn't = Current, then copy the last row and paste _
it to the next row.  Repeat this process until the last value in column CB = Current.
'Do Until csws7.Range("CB" & csLastRow7).Value = "Current"


If Not csws7.Range("CB" & csLastRow7).Value = "Current" Then
    csws7.Range("A" & csLastRow7 & ":CB" & csLastRow7).Copy
    csws7.Range("A" & csLastRow7 + 1).PasteSpecial
        csws7.Range("X" & csLastRow7 + 1).Value = ""
        csws7.Range("AH" & csLastRow7 + 1).Value = ""
        csws7.Range("AR" & csLastRow7 + 1).Value = ""
        csws7.Range("BB" & csLastRow7 + 1).Value = ""
        csws7.Range("BL" & csLastRow7 + 1).Value = ""
        csws7.Range("BT" & csLastRow7 + 1).Value = 0
        csws7.Range("BU" & csLastRow7 + 1).Value = 0
        csws7.Range("BV" & csLastRow7 + 1).Value = 0
        csws7.Range("BW" & csLastRow7 + 1).Value = 0
        csws7.Range("BX" & csLastRow7 + 1).Value = 0
End If
Application.CutCopyMode = False


[COLOR=#ff0000]csws6.Activate[/COLOR]
[COLOR=#ff0000]csws7.Activate[/COLOR]


'Loop


Application.ScreenUpdating = True


End Sub

 

[JLGWhiz]

My comment of "...performing on only one row at a time." is better described as, only 1 row was being copied and pasted. Which, as you indicate, is what the code was supposed to do. I want the copy and paste performance to continue until the last used cell in column CB = "Current".

Since this is running when the worksheet activates, I could probably just code so that tabs activate and deactivate until the condition is met, but that wouldn't seem to be efficient.

Your code only called for one row to be copied and pasted. As @HackSlash pointed out, there is nothing in the code that would make the word "Current" ever appear where the code is looking for it, ergo the continuous loop. If you have formulas in the sheet that are supposed to put the word "Current" in a cell, then it would help us to know what the formula is and where it is located at runtime. We cannot see your worksheet so you have to provide pertinent information for us to be able to give the the assistance you seek. Your overall objective is not clearly defined.

 

[reberryjr]

I'm not sure what you mean by there's "...nothing in the code that would make the word "Current" ever appear...". The code is looking for the word "Current". If it's not there, then the copy and paste actions should occur. I recognized that the code was originally only copying and pasting a single row, hence the desire to incorporate the Do Until, with the Loop.

 

[Dryver14]

You are saying do this until the last row = "Current"

If it doesn't say "Current" then copy that row to the one below it which makes that the last row

then loops to see if the last row says Current, which of course it doesn't because you just copied a line into it that doesn't say Current and so the process repeats for ever.

 

[reberryjr]

@Dryver14...yes, that is the what is happening. How can I prevent that, and continue the copy paste until "Current" appears? In thinking about the other two comments, perhaps what they're asking is, "how does "Current" get into the cell?" If so, it's a result from a formula calc in the cell.

 

[mikerickson]

It looks like what happens inside the loop doesn't change from one loop until the next.
You always copy from the same row (csLastRow7) and paste to the same row (csLastRow7 + 1)

There is no need for a loop. Just test if "Current" in the cell, if it isn't do the copy once and end. If not, end without copying.

 

[reberryjr]

The issue is, let's say that CB2 = "Late". I need to copy row 2 and paste it in row 3. If the formula in CB3 returns a value of "Late", I need the copy and paste to repeat.