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

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

What would I use to make sure they match up?

That would be my suggestion.

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?

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