VBA: Loops appear to work in debugging, but only partial results appear

[tornhair]

Hi all,

I have a macro which contains three nested 'for' loops. Stepping through the code they all appear to work as normal, but when I run the macro the results stop halfway.

I have been poring over the code for hours and cannot see why for the life of me. If anyone can spot an error or might know what's happening I will build a church unto them.

The sheet is laid out as follows:

Sheet1:
- Each row, starting with row 4 corresponds to one respondent. The ID of each respondent is contained in ColumnA of this sheet. There are 55 of these in the test sheet.

- There are nine groups of 19 columns named 'episode1-9' These contain a StartTime, EndTime and InterludeTime for that episode. Currently the sheet contains only test data, so those values are the only ones that change between episodes and currently only 4 episodes are filled.

- The time values are a number between 1 and 36. These represent timeslots.

Sheet2:
-ColumnA contains each responseID copied down 36 rows.

-ColumnB contains a value between 1 and 36, thus corresponding to Respondent 'n's activity for that time slot.

- The 11 columns after that will contain values taken from the episode ranges from sheet1 corresponding to that respondent. This area is called "Response1-55" according to which respondent it is.


To perform that step the macro should function like this:

***************
Define integer Responses = number of respondents (55)
Define counters n,r,i
For n=1 to Responses
Set a range 'current_range' from sheet 2 = Response&n
For r = 1 to 9
Set a range 'current_episode' = Episode&r from sheet 1

From that episode store the start, and and interlude times
For i = start_time to end_time
Select a part of row 'n' from Episode&r and copy it to Response&n row i
Do this until the end_time of Episode&r, then choose Episode&r+1 until you finish an episode with End_Time =36 or finish Episode9
Do all the above for the next Response.(Next n)
***************

In debugging the macro appears to work exactly like that.

When I run it in the sheet, however, it seems to work correctly except for skipping the last two episodes out of the four which contain test data.

I am absolutely stumped, have been looking at this for hours and cannot see why this should be happening.

I realise this is a long-winded post and that the operation needs explaining. I'm posting in the hope that the solution is something ridiculously simple which I have missed. Any assistance at all would be appreciated to a ridiculous degree.

Below is the code for the macro described and a link to the example sheet. I have seen it is more usual to post a screengrab of the sheet, but this one is large due to the repeating ranges, so figure this might be better.

Thanks already to anyone who even read this far.

Sub PopulateMedia()
    Application.ScreenUpdating = False

    'Count the total number of response rows in original sheet
    Dim Responses As Long, n As Integer, i As Integer, r As Integer
        Responses = (Sheets("Sheet1").UsedRange.Rows.Count - 3) ' Response rows begin from sheet1, row 4

    'For each response...
    For n = 1 To Responses
        i = 1 'Reset i for new response
            Dim curr_resp As Range
                Set curr_resp = Sheets(2).Range("Response" & n) 'Define a range containing all response data
                
            For r = 1 To 9  'For each episode...
                Dim curr_ep As Range 'Define a range containing episode data for all responses
                    Set curr_ep = Sheets(1).Range("episode" & r)
                Dim Stime As Integer, Etime As Integer, Itime As Integer 'Variables contain start, end and inter-episode times
                    Stime = curr_ep.Cells(n, 1)
                    Etime = curr_ep.Cells(n, 17)
                    Itime = curr_ep.Cells(n, 19)
                Dim media As Range 'Define a range within this episode which contains the columns to be copied
                    Set media = Sheets(1).Range("episode" & r)
                
                    For i = Stime To (Etime + Itime) 'for each time-slot...
                        If i <= Etime Then
                          Dim a As Variant
                            a = media.Range(media.Cells(n - 3, 1), media.Cells(n - 3, 11))
                            curr_resp.Rows(i) = a 'Copy data from above media for slots between Stime and Etime
                        End If
                    Next i
                If Etime = 36 Then Exit For
             Next r
     Next n

    Application.ScreenUpdating = True
End Sub

http://dl.dropbox.com/u/41041934/MrExcelExample/TornHairExampleSheet.xlsm