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.
http://dl.dropbox.com/u/41041934/MrExcelExample/TornHairExampleSheet.xlsm
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.
Code:
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