excel vba - VBA copy from a union of two ranges to a row of another range -

dear competent people.

i'm having problem following code, sub completes correctly not copy correct data correct location. repeating pattern of lines of zeros not correlate iterators in place.

i think problem copying values sub-set of range, episode&r. looked @ using union property shown wrong commenter below.

currently 9 ranges named "episode"1-9 each row of contains data 1 respondent. columns 5 through 15 of these ranges contain data copied, therefore range copied each respondent is: row i, columns 5 through fifteen. step stuck with.

if copy it, data end on sheet2 range has been named each respondent, called respondent&n. rows of response&n represent time slots during episode&r can occur. outside of slots episode&r occurs there can zeroes, isn't necessary.

the logical structure appears work fine. have watched local values counters closely in debugging , work supposed to.

i looking @ using range.item method select row 'n', columns 5-15 episode&r, cannot right.

any assistance @ appreciated.

a link example sheet here: http://dl.dropbox.com/u/41041934/stackoverflow/tornhairexamplesheet.xlsm

sub populatemedia() application.screenupdating = false sheets(1).activate  'count total number of response rows in original sheet dim responses long, n integer, integer, j integer, r integer responses = sheets("sheet1").range("a:a").end(xldown).row  'for each response... n = 1 responses dim curr_resp range set curr_resp = sheets(2).range("response" & n) 'define range containing response data     r = 1 9 'for each episode...         dim curr_ep range 'define range containing episode data responses         set curr_ep = sheets(1).range("episode" & r)  'variables contain start, end , inter-episode times         dim stime integer, etime integer, itime integer      stime = curr_ep.cells(n, 1).value     etime = curr_ep.cells(n, 16).value     itime = curr_ep.cells(n, 18).value  'define range within episode contains columns copied  dim media range      sheets(1).activate     set media = set media = sheets(1).range("episode" & r).item(n, "5:15") 'range copied union of active episode , active response.***this line incorrect, example purpose.      sheets(2).activate  'for each time-slot...***this section i'm having trouble         = 1 (etime + itime)              if > etime 'fill response range zeros time slots outside stime , etime             sheets(2).range("response" & n).rows = 0              elseif >= stime 'copy data above union slots between stime , etime             sheets(2).range("response" & n).rows(i) = media              else 'stick zeroes until new 'r' means new episode***             sheets(2).range("response" & n).rows(i) = 0              end if         next     next r next n end sub 

to honest, spreadsheet real mess, why find difficult work it!

anyway, trying achieve seems be: in range named episode1, capture row number corresponds i-th respondent , copy information second sheet. , each episode , respondent. if case, code below seems doing want. not clean , improved further.

sub populatemedia()     application.screenupdating = false      'count total number of response rows in original sheet     dim responses long, n integer, integer, j integer, r integer     responses = sheets("sheet1").range("a:a").end(xldown).row      'for each response...     n = 1 responses         dim curr_resp range         set curr_resp = sheets(2).range("response" & n) 'define range containing response data         r = 1 9 'for each episode...             dim curr_ep range 'define range containing episode data responses             set curr_ep = sheets(1).range("episode" & r)             dim stime integer, etime integer, itime integer 'variables contain start, end , inter-episode times             stime = curr_ep.cells(n, 1)             etime = curr_ep.cells(n, 16)             itime = curr_ep.cells(n, 18)             dim media range 'define range within episode contains columns copied             set media = sheets(1).range("episode" & r)             = 1 (etime + itime) 'for each time-slot...***this section i'm having trouble                 if > etime                   curr_resp.rows(i) = 0 'fill response range zeros time slots outside stime , etime                 elseif >= stime                   dim variant                   = media.range(media.cells(n, 5), media.cells(n, 15))                   curr_resp.rows(i).resize(1, 11) = 'copy data above union slots between stime , etime                 else                   curr_resp.rows(i) = 0 'stick zeroes until new 'r' means new episode***                 end if             next         next r     next n      application.screenupdating = true end sub