Do loop won't stop!

[laxster]

Hi,

I'm having some issue where a Do Loop is blowing past the point when it's supposed to stop. Once it hits a XXXXXXXX it should stop, but instead it keeps running until it hits the maximum of Excel records. I pasted the code below.

Any ideas?

Sub DRP_DOH_Report_Formatting()

    Range("C65536:C65536").End(xlUp).Select
    ActiveCell.Offset(1, 2).Select
    ActiveCell.FormulaR1C1 = "XXXXXXXX"
    ActiveCell.Offset(0, -4).Select
    ActiveCell.FormulaR1C1 = "XXXXXXXX"

'POPULATE PLANT DESCRIPTION IN COL. F
    Range("e1").End(xlDown).Select
    Selection.Copy
    ActiveCell.Offset(2, 0).Select
    Range(Selection, Selection.End(xlDown).Offset(-1, 0)).Select
    ActiveSheet.Paste
     
Do Until ActiveCell.Value = "XXXXXXXX"
    Selection.End(xlDown).Select
    Selection.Copy
    Range(Selection, Selection.End(xlDown).Offset(-1, 0)).Select
    ActiveSheet.Paste
Loop

 

[SOS]

Setting the formula to XXXXXXXX isn't the same as setting the VALUE to XXXXXXXX.

 

[laxster]

What would I use to make sure they match up?

 

[SOS]

That would be my suggestion.

 

[laxster]

It still seems to run without stopping. Here's the code using value instead of Formula:

    Range("C65536:C65536").End(xlUp).Select
    ActiveCell.Offset(1, 2).Select
    ActiveCell.Value = "XXXXXXXX"
    ActiveCell.Offset(0, -4).Select
    ActiveCell.Value = "XXXXXXXX"

'POPULATE PLANT DESCRIPTION IN COL. F
    Range("e1").End(xlDown).Select
    Selection.Copy
    ActiveCell.Offset(2, 0).Select
    Range(Selection, Selection.End(xlDown).Offset(-1, 0)).Select
    ActiveSheet.Paste
     
Do Until ActiveCell.Value = "XXXXXXXX"
    Selection.End(xlDown).Select
    Selection.Copy
    Range(Selection, Selection.End(xlDown).Offset(-1, 0)).Select
    ActiveSheet.Paste
Loop

Did I overlook something?

 

[Brianwarnock]

 Range("C65536:C65536").End(xlUp).Select
    ActiveCell.Offset(1, 2).Select
    ActiveCell.Value = "XXXXXXXX"
    ActiveCell.Offset(0, -4).Select
    ActiveCell.Value = "XXXXXXXX"

That code finds the last entry in col C and then places XXXXs in cols E and A 1 row down

'POPULATE PLANT DESCRIPTION IN COL. F
    Range("e1").End(xlDown).Select
    Selection.Copy
    ActiveCell.Offset(2, 0).Select
    Range(Selection, Selection.End(xlDown).Offset(-1, 0)).Select
    ActiveSheet.Paste

I presume that that code was to propagate what was in E1 down to the XXXXs, even if you mention Col F. But the first line of code will return the XXXXs So insert as Line 2

ActiveCell.End(xlUp).Select


Now What is this code supposed to do? It will copy the XXXXs in Col E to the bottom row of the Sheet.

Do Until ActiveCell.Value = "XXXXXXXX"
    Selection.End(xlDown).Select
    Selection.Copy
    Range(Selection, Selection.End(xlDown).Offset(-1, 0)).Select
    ActiveSheet.Paste
Loop

BTW your last statement after doing copy and pastes should be

Application.CutCopyMode = False

And after you have it working wrap the code in

Application.ScreenUpdating = False
your code
Application.ScreenUpdating = True

For better performance

Brian