excel - Looping through numbers to create a large table -

i have code works, want add more functionality it. supposed do, , has sped processes, think can sped more. using solution marked answered here: using vba threshold value

but

i have code:

sub outputenergytoallsheets() dim w each w in thisworkbook.worksheets     if not instr(w.name, "total") > 0 , not instr(w.name, "ev")         outputenergytosheet w.name     end if next w end sub  sub outputenergytosheet(thesheet string) 'y = columns check: 2-25 'x = rows check: 2-152 'z = check next 4 cells dim x, y, z, check 'clear range store #n/a or energy outputs sheets(thesheet)     .range("b153:y153") = vbnullstring     y = 2 25         x = 2 152             if .cells(x, y) > .range("z2")  'if value greater z2                 check = true                   'let's check next 4                 z = 1 30                'if of them fail                     if .cells(x + z, y) < .range("z2")                         check = false          'the check fails                         exit                     end if                 next z                 if check = true                    'if check doesn't fail                     .cells(153, y) = int(.cells(x, 1))  'set cell 153 energy level                     exit                 end if             end if         next x                                   'if no energy level set - #n/a         if .cells(153, y) = vbnullstring .cells(153, y) = ""     next y end end sub 

but line says:

for z = 1 30 

i having change 0 100 in increments of 1. outputs these values should on worksheets , go sub , increase value , repeat. values output in each worksheet except few in row 153. there way have 0 in row 153, 1 in 154, 2 in 155, etc 100? understand if not possible, me ton of time, because have go through process many workbooks. if can done save me several monotonous hours of busy-work. anyways, reading.

for first code block, tried stay general structure of code in question. have example swapped out innermost 2 for loops single while loop. more efficient requires significant logic change. did make changes though. set "n/a" @ beginning instead of end , added condition last if statement. implement new functionality of checking variable numbers of consecutive cells, included for loop counter k around for loop counter z , made end point of z dependent on k. print out row 152 + k.

sub outputenergytosheet(thesheet string)     'y = columns check: 2-25     'x = rows check: 2-152     'k = number of matches in row find     'z = check next (k - 1) cells     dim x, y, z, check, k     'clear range store n/a or energy outputs     sheets(thesheet)         .range("b153:y252") = "n/a"         y = 2 25             x = 2 151                 if .cells(x, y) > .range("z2")  'if value greater z2                     k = 1 100                         check = true                   'let's check next k - 1                         z = 1 k - 1             'if of them fail                             if .cells(x + z, y) <= .range("z2")                                 check = false          'the check fails                                 exit                             end if                         next z                         if check = true , .cells(152 + k, y) = "n/a"                             .cells(152 + k, y) = int(.cells(x, 1))                         end if                     next k                 end if             next x         next y     end end sub 

before did this, threw own method cleaner , runs much faster. code below steps down rows , maintains running count of how many consecutive matches has found. eliminates lot of checks because checks given cell once. down 2 total loops! code above checking cell many times on in inner loops. below code better maintaining values in array (read/write in excel slow) and/or maintaining counter of maximum length have achieved current column. stored of numbers variables can , confidently modified.

sub efficientenergy(ws worksheet)     dim r integer, c integer, ctr integer     dim compval double     dim maxrow integer, maxcol integer, maxconsecutive integer     maxrow = 151     maxcol = 25     maxconsecutive = 100     compval = ws.cells(2, 26).value     ws.range(ws.cells(maxrow + 2, 2), ws.cells(maxrow + maxconsecutive + 1, maxcol)).value = "n/a"     c = 2 maxcol         ctr = 0         r = 2 maxrow             if ws.cells(r, c).value > compval                 ctr = ctr + 1                 if ws.cells(maxrow + 1 + ctr, c).value = "n/a"                    ws.cells(maxrow + 1 + ctr, c).value = ws.cells(r - ctr + 1, 1).value                 end if             else                 ctr = 0             end if         next r     next c end sub 

the code using call these methods in testing (just comment out whichever 1 aren't using):

public sub getconsecutivevals()     'outputenergytosheet ("sheet1")     call efficientenergy(activeworkbook.worksheets("sheet1")) end sub 

or run on every worksheet in active workbook (untested):

public sub getconsecutivevals()     dim ws worksheet     each ws in activeworkbook.worksheets         'outputenergytosheet (ws.name)         call efficientenergy(ws)     next ws end sub 

place code in module in workbook. open workbook data in sheet1 (or change code above sheet name). hit alt + f8 , run getconsecutivevals routine. if don't see method in dialog window, make sure workbook code , workbook data in same excel application window