Loop doesn't stop when it sees "Stop"

[Oscarr]

I have the following code that is supposed to stop running when the active cell contains the text "Stop", but it doesn't stop. I have several macros designed to stop in the exact same way and the do stop, but this one doesn't. Can anyone tell me why it keeps going?

Here is the code:

Sub newhires()
'
' newhires Macro
'


'
Range("ac2").Select
Do Until ActiveCell = "Stop"
Selection.End(xlDown).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
ActiveCell.Offset(0, -13).Select
Range(Selection, ActiveCell.Offset(0, 5)).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveCell.Offset(0, 13).Select

Loop
End Sub

 

[RCBricker]

Ok I have some questions.

It looks to me that you recorded this macro. So Kudoos for trying to figure it out.

It looks to me that your task is to start with cell AC2
move to the bottom row of data in column AC
move to the last column of data
copy that cell
move to the left 13 columns
move back to the right 5 columns
paste the values into that cell
move right 13 columns

is all that correct?

This might be easier if you just state what you are trying to do.

I think you are trying to copy the last cell in the last column of data and paste it to a new cell (what column is that cell in?).

If that is true then why the loop? there is nothing below the last row or to the right of the last column.

Also to answer your initial question your line should read:

do until activecell.value="Stop"

Rich

 

[Oscarr]

Thanks for the reply. Here's what I'm trying to accomplish:


Start in cell AC2.
Move down to the 1st cell that contains data
Select that specific cell and 5 cells to the right (columns AC through AH).
Copy the data in this range
Move 13 columns to the right (to column P)
Select that specific cell and 5 cells to the right (columns P through U).
Paste (values) the data from the copied range (columns AC through AH) into the selected range (columns P through U)
Return back to starting point (column AC)
Move down to the next cell that contains data and repeat the selecting, copying, pasting, and returning process above.
Stop when the active cell in column AC contains the text "Stop"


Let me know if this makes more sense in terms of what I'm trying to do.


Thanks,
Jeff

 

[RCBricker]

ok got it.

couple questions and I can write this out.

1. are the cells always the same (always AC - AH and always P - U)?

2. do you always start with your headers in the same row? if so we can eliminate the AC2 the move down to the first cell with data. We can write it to start at AC and the row your data always starts on.

3. Why is there a cell that says stop? Why not to the end of the data?

I will write this code with the following assumptions and then change it based on the answer you give above.

Assumptions:

1. the columns are always the same

2. start row changes

3. There are cells that say stop.

Rich

 

[RCBricker]

This should do the trick

Sub newhires()

Dim rng As Range, cell As Range, rngCOPY As Range, rngDEST As Range
Dim Lrow As Long, lStop As Long
Dim sh As Worksheet
Dim wb As Workbook
Dim i As Variant, j As Variant

    Set wb = ThisWorkbook
    Set sh = Sheets("") ''' ******* add the sheet name to this code better than activesheet
    Set sh = ActiveSheet    '''***** Delete this line once you have added sheet name above
    
    With sh
        Lrow = Range("AC" & .Rows.Count).End(xlUp).Row  ' Finds the last used row in col AC
        Set rng = Range("AC2").End(xlDown)  'sets rng to first row of data in AC
        j = rng.Row     ' gets the row number of first row of data
        Set rng = Range(.Cells(rng.Row, rng.Column), .Cells(Lrow, rng.Column))  ' _
            re-sets rng to include all data in column AC

        '   loop to find the first cell in AC that has the word stop as a value _
            the if statement looks at the cell value for the word stop _
            once found sets the lStop variable to that row number _
            exits the loop as we only want the first instance
        For Each cell In rng
            If cell.Value = "stop" Or cell.Value = "Stop" Or cell.Value = "STOP" Then
                lStop = cell.Row
                Exit For
            End If
        Next cell
        
        '   loop to copy the data from columns AC - AH and Paste to Columns _
            P - U (only pastes the values)
        For i = j To lStop
            Set rngCOPY = Range(.Cells(i, 29), .Cells(i, 34))
            Set rngDEST = Range(.Cells(i, 16), .Cells(i, 21))
            rngCOPY.Copy
            rngDEST.PasteSpecial xlPasteValues
            i = i + 1
        Next
    
    End With
 
 End Sub

Rich

 

[Oscarr]

Thanks Rich. Answers to your questions are:
1. Yes
2. Headers are always in the same row.
3. I use Stop because I don't know how to write code that ends at the end of data. Using Stop is my low rent solution.

I used the code you wrote above and for some reason it skips some of the lines that contain data. I can't figure out why it does this.

 

[RCBricker]

ok well I am heading home for the weekend. If you are still in need of help on Monday I will take a look and we can figure it out. The code shouldn't be skipping anything. as for how to stop at the end of data. Look at the code I wrote. you would loop through all of the cells in the range with a FOR EACH Loop. you find the bottom cell of the data with the Lrow variable. I used this method when I looked through your data for the first cell with Stop. if there was no stop the loop would have look throughout the range and stopped with the last cell.

rich

 

[RCBricker]

Still need help Oscarr?

Rich