Long Loop Accidentally Repeating Numbers

[AlexSlivi]

I was spot-checking the code I wrote, which goes through 17000 people, performs some calculations for them, and then spits the data out on another tab. I assigned an ID number to each person for easy checking.

Occasionally, in the loop, I'll spot things like (for ID):
456
457
457
459

Then I'll re-run it and that problem will be gone, but will appear somewhere else (or if I plug in 458, all the information I need is there and correct). Is this a CPU overflow issue? Any idea on how to correct this?

Thanks,
Alex

 

[shg]

Kinda hard to guess without seeing the code ...

 

[AlexSlivi]

I don't know how much the code will help, as most of the caluclations are done in the workbook, but here you are:

For x = 1 To 17402
Sheets("InputsAndResults").Range("B5").Value = x
Sheets("2011").Range("A3:F3").Offset(x, 0).Value = Sheets("Individual Results").Range("A3:F3").Value
Sheets("2011").Range("I3:AF3").Offset(x, 0).Value = Sheets("Individual Results").Range("G3:AD3").Value
Sheets("2011").Range("G3:H3").Offset(x, 0).Value = Sheets("Individual Results").Range("C10:D10").Value
Next

 

[shg]

Presumably changing InputsAndResults!B5 causes a calculation to occur on Individual Results?

    With Worksheets("2011")
        For x = 1 To 17402
            Worksheets("InputsAndResults").Range("B5").Value = x
            Worksheets("Individual Results").Calculate
            .Range("A3:F3").Offset(x).Value = Worksheets("Individual Results").Range("A3:F3").Value
            .Range("G3:H3").Offset(x).Value = Worksheets("Individual Results").Range("C10:D10").Value
            .Range("I3:AF3").Offset(x).Value = Worksheets("Individual Results").Range("G3:AD3").Value
        Next x
    End With

Shouldn't be necessary, though.

 

[AlexSlivi]

I don't think that's going to work, because I also have the loop doing the same things for the 2012 and 2013 sheets (I just took them out to prevent a huge wall of text--I'm sorry about that). Maybe something like this would work (to repeat the step), but I don't think I can change x from inside the loop can I?

If Worksheets("Individual Results").Range("B3").Value <> x Then
x = x - 1
End If

 

[AlexSlivi]

I appreciate your help, by the way.

 

[shg]

You're welcome, but I don't have enough information to make a suggestion -- except to say it is never correct to change the loop index variable inside a loop.

 

[AlexSlivi]

Okay. For anyone who stumbles upon this later, I restarted my computer and it worked fine (I've coded it to notify me if it the loop makes this error, and I haven't been notified yet). So I'm guessing it was more of a processing error.

 

[shg]

Glad you got it sorted.