Excel 2007 : Macro - loop slows down

I created a macro that runs an iterative process. The macro basically makes some adjustments to some data and copies the data to a blank row. Then, the macro does the same thing over and over.

There are 13000 rows the macro cycles through and I noticed around row 4000, the macro's speed essentially comes to a stop. I also noticed at row 4000 my computer usage (via the task manager) jumps from 60% to 90%.

I assume it is not a coding issue since the macro runs for awhile, but then hits a wall in the middle of the iterative portion.

Does anyone have ideas as to what is going on?

Welcome to the forum,

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

And when you do that, post the code. No one can give you advice on a code problem sight-unseen.

shg, I'll let you have the honors....

IALTO,

We're happy to have you here, and more than willing to help -- provided you first take a few minutes to read ALL the forum rules, and then bring this thread into compliance. For now, that means changing the thread title and editing your post to add CODE tags.

ExpRate is undefined.

As a row variable, i should be declared as a Long, not an Integer.

You're writing to the statusbar in every loop iteration. You could do it every 10th or 100th iteration.

If your changes are causing portions of the workbook to recalculate, you should turn calculation off and calculate only whatever portion you need to within the loop.

You make repeated and unnecessary references to ThisWorkbook, which could be eliminated with a With statement or setting some new object variables.

You've set the three range variables to the same range -- why?

So there's a lot you could do, but I can't tell what the whole procedure is doing, so it's hard to be specific.

How big is range ER?

Without seeing the workbook, or knowing if a loop is even necessary, ...

Option Explicit

Sub SG()
    Dim rExp        As Range
'    Dim rCom        As Range
'    Dim rPrm        As Range
    Dim rInp        As Range
    Dim rERC        As Range
    Dim rERP        As Range

    Dim iRow        As Long
    Dim nRow        As Long

    With ThisWorkbook
        Set rExp = .Worksheets("MGN").Range("ER")
'        Set rCom = .Worksheets("MGN").Range("ER")
'        Set rPrm = .Worksheets("MGN").Range("ER")
        Set rInp = .Worksheets("Input").Range("MacroInput")
        Set rERC = .Worksheets("MGN Detail").Range("ERCopyRange")
        Set rERP = .Worksheets("MGN Detail").Range("ERPasteTarget")
    End With

    nRow = rExp.Rows.Count

    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
        rInp.ClearContents

        For iRow = 1 To rExp.Rows.Count
            If iRow Mod 100 = 0 Then Application.StatusBar = "Row " & iRow & " of " & nRow
            rInp.Value = rExp(iRow, 1).Value
            ' calculate something here if necessary
            rERP.Offset(iRow - 1).Value = rERC.Value
        Next iRow

        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
    End With
End Sub

Sure, that's a good guess. If so,

Range("Target").Value = Range("Source").Value2