Do Until Endless Loop

R

reberryjr

Well-known Member
Joined
Mar 16, 2017
Messages
701
Office Version
  1. 365
Platform
  1. Windows
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?

Code: 
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
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Sort by date Sort by votes
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.
 
Upvote 0
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.
 
Upvote 0
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.

Code: 
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
 
Upvote 0
reberryjr said:
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.
Click to expand...

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.
 
Last edited:
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
@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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

Agnarr
  • Solved
Checkbox Timestamp with VBA
Replies
3
Views
296
bobsan42
bobsan42
sparky2205
  • Solved
#BLOCKED! error
2
Replies
10
Views
409
sparky2205
sparky2205
B
  • Solved
Do Until loop nested in For loop, How advance to the "Next x" in the For Loop
Replies
4
Views
302
CephasOz
CephasOz
S
  • Question
VBA loop a countif function in a moving range
Replies
8
Views
158
Cubist
Cubist
A
  • Solved
Error 1004 no cells found
Replies
3
Views
372
Arcadian
A

Forum statistics

Threads
1,215,678
Messages
6,126,176
Members
449,296
Latest member
tinneytwin

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top