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

 

[mikerickson]

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.

Repeat until when?

 

[reberryjr]

Repeat until the formula in CB returns a result of "Current". Perhaps a visual aid will help me describe the goal.

Pymt Status is column CB. It has this formula in it.
=IF(BS2=0,"Current",IF(CA2<=0,"Paid",IF(OR(M2>0,T2>0,AC2>0,AM2>0,AW2>0,BG2>0,BQ2>0),"Late")))

[TABLE="class: grid, width: 100"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Pymt Status[/TD]
[/TR]
[TR]
[TD]Late[/TD]
[/TR]
[TR]
[TD]Late[/TD]
[/TR]
[TR]
[TD]Late[/TD]
[/TR]
[TR]
[TD]Late[/TD]
[/TR]
[TR]
[TD]Current
[/TD]
[/TR]
</tbody>[/TABLE]

The first row gets populated via a user form. The goal is to keep adding records until the formula returns "Current".

 

[Dryver14]

The issue here is that you are copying down the entire row, I really dont think you want to be doing that.

are you just trying to copy down on one column?

 

[reberryjr]

I want to copy the entire row, as it has other data points that perform other calculations, such as calculating the next payment due, the payment amount just to name a couple.

 

[Dryver14]

I'm sorry I give in.

If you take the last row, in it's entirety and copy it down one row then everything is exactly as it was on the line before.

You are wording something wrong in the question, just have a think

 

[JLGWhiz]

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.

To get the code to exit the loop, something must happen that changes the test cell to the value you are testing for. Your code does not do that and it will continue to loop until you put something in the code or on the worksheet that causes the test cell to acquire the test value.

x = 2
Do until Range("A1") = "Blah"
    Row(x).Copy Range("A" & x + 1)
       [COLOR=#FF0000] If x = 4 Then Range("A1") = "Blah"[/COLOR]
    x = x + 1
Loop

this loop would stop when x = 5 because Cell A1 was changed to "Blah" when x reached a value of 4. Your code does not have anything in it ot change the value of the cell you are evaluating.

 

[JLGWhiz]

After re-reading all the posts, I think your problem could be solved by changing your macro type from a Worksheet_Activate to a Worksheet_Calculate or Worksheet_Change and do away with the Do Loop. It looks like the change in column BS is determined by manual input somewhere that generates calculations.

 

[mikerickson]

Repeat until the formula in CB returns a result of "Current".

What is the formula in CB?

 

[JLGWhiz]

What is the formula in CB?

The OP is using this formula in column CB

=IF(BS2=0,"Current",IF(CA2<=0,"Paid",IF(OR(M2>0,T2>0,AC2>0,AM2>0,AW2>0,BG2>0,BQ2>0),"Late")))

to evaluate eight cells and return one of three possible values. But the Loop does nothing to change the value in any of the eight cells, ergo, endless loop. IMHO, the loop is not needed. A different event seems the best approach to me, with the loop abandoned.

 

[mikerickson]

I agree that the loop isn't needed.
Current and Late sound like results from new data entry rather than copying existing data.
I wonder if the goal of the OP code is to prepare blank template rows for future data entry.

I also wonder if the LastRow line should be inside the loop. (Although looping is probably a mistake)