excel - VBA - Loop and Inefficient If Statements -

i have written vba code passable, takes long time , difficult upkeep. use roll several sub departments single department. basically, have 2 columns:

"a" - contains 5 digit facility numbers

"c" - contains 5 digit department numbers

my code loops through each row , replaces department numbers if facility , department match condition:

sub dept_loop()      dim long     dim lrow long  lrow = cells(rows.count, "a").end(xlup).row  = 1 lrow      if cells(i, "a") = 10000 , cells(i, "c") = 11040         cells(i, "c") = 11000     elseif cells(i, "a") = 10000 , cells(i, "c") = 11040         cells(i, "c") = 11000     elseif cells(i, "a") = 10000 , cells(i, "c") = 11050         cells(i, "c") = 11000     elseif cells(i, "a") = 10000 , cells(i, "c") = 11060         cells(i, "c") = 11000     elseif cells(i, "a") = 10000 , cells(i, "c") = 11070         cells(i, "c") = 11000     elseif cells(i, "a") = 21000 , cells(i, "c") = 10120         cells(i, "c") = 10130     elseif cells(i, "a") = 21000 , cells(i, "c") = 10160         cells(i, "c") = 10050     elseif cells(i, "a") = 22000 , cells(i, "c") = 11910         cells(i, "c") = 10000     elseif cells(i, "a") = 22000 , cells(i, "c") = 11915         cells(i, "c") = 10000     elseif cells(i, "a") = 22000 , cells(i, "c") = 14800         cells(i, "c") = 14000     elseif cells(i, "a") = 22000 , cells(i, "c") = 14820         cells(i, "c") = 10000     elseif cells(i, "a") = 22000 , cells(i, "c") = 15700         cells(i, "c") = 20040     elseif cells(i, "a") = 22000 , cells(i, "c") = 20420         cells(i, "c") = 20400     elseif cells(i, "a") = 22000 , cells(i, "c") = 20440         cells(i, "c") = 20400     elseif cells(i, "a") = 22000 , cells(i, "c") = 21190         cells(i, "c") = 21000     elseif cells(i, "a") = 22000 , cells(i, "c") = 21195         cells(i, "c") = 21000     elseif cells(i, "a") = 23000 , cells(i, "c") = 10760         cells(i, "c") = 10750     elseif cells(i, "a") = 23000 , cells(i, "c") = 11030         cells(i, "c") = 14000     elseif cells(i, "a") = 23000 , cells(i, "c") = 11360         cells(i, "c") = 11300     elseif cells(i, "a") = 23000 , cells(i, "c") = 11370         cells(i, "c") = 10000     elseif cells(i, "a") = 23000 , cells(i, "c") = 11600         cells(i, "c") = 11700     elseif cells(i, "a") = 23000 , cells(i, "c") = 11620         cells(i, "c") = 11700     elseif cells(i, "a") = 23000 , cells(i, "c") = 11660         cells(i, "c") = 11700 end if  next  end sub 

is there better way doing this? loop through hundreds of thousands of records , takes forever..

edit* i've had chance build out , give try. i've encountered error cannot figure out. i'm getting runtime error '424': object required first .autofilter in loop.

@nutsch or @dan -- ideas?

here new code i've written:

sub dept_loop()

dim bu variant, dept variant, newdept variant dim lrow long, lcolumn long  'array of facilities/business units (roll from) bu = array(10000, 10000, 10000, 10000, 10000, 21000, 21000, 22000, _            22000, 21000, 21000, 23000, 23000, 22000, 21000, 21000, _            21000, 22000, 24000, 21000, 21000, 24000, 21000, 21000, _            23000, 22000, 21000, 22000, 21000, 25000, 23000, 25000, _            22000, 22000, 22000, 24000, 24000, 23000, 23000, 22000, _            22000, 24000, 23000, 23000, 25000, 25000, 23000, 25000, _            24000, 23000, 23000, 25000, 25000, 25000, 24000, 24000, _            25000, 25000, 21000, 21000, 21000, 22000, 22000, 23000, _            23000, 22000, 24000, 24000, 25000, 25000, 21000, 21000, _            21000, 21000, 22000, 22000, 22000, 22000, 23000, 23000, _            22000, 22000, 23000, 23000, 23000, 21000, 24000, 24000, _            24000, 24000, 25000, 22000, 25000, 25000, 25000, 23000, _            24000, 25000, 22000, 21000, 22000, 23000, 24000, 25000, _            21000, 22000, 21000, 22000, 23000, 24000, 25000, 22000)  'array of departments (roll from) dept = array(11040, 11040, 11050, 11060, 11070, 10120, 10160, 10120, _              10160, 10760, 11030, 10120, 10160, 10760, 11360, 11370, _              11371, 11030, 10120, 11570, 11600, 10160, 11620, 11660, _              10760, 11360, 11910, 11370, 11915, 10120, 11030, 10160, _              11600, 11620, 11660, 10700, 10760, 11360, 11370, 11910, _              11915, 11030, 11600, 11620, 10700, 10701, 11660, 10760, _              11370, 11910, 11915, 11030, 11360, 11370, 11910, 11915, _              11910, 11915, 14800, 14820, 14840, 14800, 14820, 14800, _              14820, 15700, 14800, 14820, 14800, 14820, 20420, 20440, _              21190, 21195, 20420, 20440, 21190, 21195, 20420, 20440, _              21800, 21820, 21155, 21190, 21195, 23250, 20440, 21155, _              21190, 21195, 20440, 23250, 21155, 21190, 21195, 23250, _              23250, 23250, 26500, 28950, 28950, 28950, 28950, 28950, _              39011, 39011, 46100, 46100, 46100, 46100, 46100, 88220)  'array of new departments (roll to) newdept = array(11000, 11000, 11000, 11000, 11000, 10130, 10050, 10130, _                 10050, 10750, 14000, 10130, 10050, 10750, 11300, 10000, _                 10130, 14000, 10130, 10000, 11700, 10050, 11700, 11700, _                 10750, 11300, 10000, 10000, 10000, 10130, 14000, 10050, _                 11700, 11700, 11700, 10000, 10750, 11300, 10000, 10000, _                 10000, 14000, 11700, 11700, 10000, 10000, 11700, 10750, _                 10000, 10000, 10000, 14000, 11300, 10000, 10000, 10000, _                 10000, 10000, 14000, 10000, 10000, 14000, 10000, 14000, _                 10000, 20040, 14000, 10000, 14000, 10000, 20400, 20400, _                 21000, 21000, 20400, 20400, 21000, 21000, 20400, 20400, _                 25040, 24400, 21150, 21000, 21000, 23200, 20420, 21150, _                 21000, 21000, 20420, 23200, 21150, 21000, 21000, 23200, _                 23200, 23200, 26700, 22000, 22000, 22000, 22000, 22000, _                 39000, 39000, 10000, 10000, 10000, 10000, 10000, 10000)  'application.screenupdating = false  lrow = range("a" & rows.count).end(xlup).row lcolumn = cells(1, columns.count).end(xltoleft).column  range(cells(1, 1).address, cells(lrow, lcolumn).address).autofilter      x = lbound(bu) ubound(bu)         .autofilter field:=3, criteria1:=dept, operator:=xlfiltervalues         .autofilter field:=1, criteria1:=bu         .autofilter.columns(3).resize(.rows.count - 1).offset(1). _         specialcells(xlcelltypevisible).value = newdept      next  end 

end sub

final edit* ended getting code work, tried l42's solution found faster autofiltering. l42's code end using. thanks!

try this:

sub conscious()     dim mularr, resarr, rngarr, pos     dim long, lrow long, x long      ' multiply value1 , value2     mularr = array(110400000, 114040000, 110500000, 110600000, 110700000, _                    212520000, 213360000, 262020000, 262130000, 325600000, _                    326040000, 345400000, 449240000, 449680000, 466180000, _                    466290000, 247480000, 253690000, 261280000, 261510000, _                    266800000, 267260000, 268180000)     ' result array     resarr = array(11000, 11000, 11000, 11000, 11000, 10130, 10050, 10000, 10000, 14000, _                  10000, 20040, 20400, 20400, 21000, 21000, 10750, 14000, 11300, 10000, _                  11700, 11700, 11700)      sheets("sheet1") ' try explicit         lrow = .range("a" & .rows.count).end(xlup).row         rngarr = .range("a1:c" & lrow) ' use 2d array         = lbound(rngarr, 1) ubound(rngarr, 1) ' manipulate array             x = rngarr(i, 1) * rngarr(i, 3): pos = application.match(x, mularr, 0)             if not iserror(pos) rngarr(i, 3) = application.index(resarr, pos)         next         .range("a1:c" & lrow) = rngarr ' return array range     end end sub 

first, need create new array mularr multiplication of values.
create second array resarr contains resulting values.
transfer range value in 2d array rngarr (it automatic) , manipulate it.
, finally, transfer range.
have added comments in actual code shouldn't hard follow.

speed: took 2.12 secs in machine dealing 100k data. think can rival autofilter in terms of speed.