string - VBA Variant Data with Subscript out of Range -

i'm bit new excel vba , after research pieced working code (seen @ bottom). based off code , few other things found created mess:

sub translatenewbom()   dim newfootprint variant dim translated variant dim temp string dim n long dim maxrow integer      maxrow = n     n = n + 1 loop until cells(n, 3).value = "stop"  cells(3, 8).entirecolumn.insert  newfootprint = range(cells(3, 7), cells(maxrow, 7)).currentregion.value translated = range(cells(3, 8), cells(maxrow, 8)).currentregion.value   = 3 maxrow     temp = newfootprint(i, 7)  'this line gives me error     temp = left(temp, 3)     if temp = ""         cells(i, 5).value = "void"     end if     if temp = "cap"         translated(i, 8).value = "smc" & right(newfootprint(i, 7).text, _         len(newfootprint(i, 7).text) - 3)         translated(i, 8) = replace(translated(i, 8).text, " ", "-")     end if next  end sub 

this error doesn't make sense me; because don't understand variants or arrays in vba. when compare other code made syntax near identical in regards newfootprint. difference being numbers involved larger. here working code, it's work in progress due horrible naming convention. newfootprint ~= datarangenew in mind.

edit: bottom code doesn't work anymore. don't remember changing anything, same out of range error pops up. i'm losing hair speak.

sub getandsortbom() ' data imported through template raw database.  ' variable declarations dim xnum integer dim maxrows long dim rng variant dim datarangenew variant dim datarangeold variant dim datarangenewfoot variant dim datarangenewto variant dim datarangenewfootto variant dim irow long dim rows long dim maxcols long dim myvarold string dim myvarnew string dim temp() string   datarangenew = range(cells(2, 12), cells(1587, 12)).currentregion.value         ' these work datarangenewfoot = range(cells(2, 13), cells(1587, 13)).currentregion.value     ' , store data in datarangeold = range(cells(3, 3), cells(maxrows, 3)).currentregion.value        ' columns without cell datarangenewto = range(cells(3, 8), cells(maxrows, 8)).currentregion.value      ' manipulation. datarangenewfootto = range(cells(3, 7), cells(maxrows, 7)).currentregion.value  ' data go through without rng = range(cells(3, 7), cells(maxrows, 8)).currentregion.value numrows = range(cells(2, 12), cells(1587, 12)).currentregion.rows.count   rows = 3 maxrows     myvarold = datarangeold(rows, 3)     irow = 1 numrows                                          ''''''''''''''''''''''''''''''''''''''''''''''         myvarnew = datarangenew(irow, 12)' why work, not other code? '                                          ''''''''''''''''''''''''''''''''''''''''''''''         if myvarold = myvarnew             datarangenewto(rows, 8) = datarangeold(rows, 3)             datarangenewfootto(rows, 7) = datarangenewfoot(irow, 13)         end if     next irow next rows  ' combines 2 columns of new data 2d array redim temp(1 maxrows, 1 2)  ' puts information 2d array = 3 maxrows     rng(i, 7) = datarangenewfootto(i, 7)     rng(i, 8) = datarangenewto(i, 8) next  ' puts 2d array in cells range(cells(3, 7), cells(maxrows, 7)).currentregion = rng   end sub 

the error runtime error '9': subcript out of range. me seems it's in range; @ least when compare old code. help?

in first case:

newfootprint = range(cells(3, 7), cells(maxrow, 7)).currentregion.value 

will not create array 7 columns, because it's single column. :-) so, trying index newfootprint(i, 7) won't work. should initialize variant this:

newfootprint = range(cells(3, 7), cells(maxrow, 7)).value 

or, better:

with worksheets("whatever worksheet use")     ' ...     newfootprint = .range(.cells(3, 7), .cells(maxrow, 7)).value     ' ... end 

and access this:

newfootprint(i, 1) 

later edit: getting data in range (r1, c1) (r2, c2), r2 ≥ r1 , c2 ≥ c1, create array (1 r2-r1+1, 1 c2-c1+1). should check all loops , all indices.