vba - Filter and Replace in Different Column -

i have data in following format:

table 1, 65,000 rows:

food          type mango         fruit mango         fruit mango         vegetable pineapple     fruit cabbage       flower carrot        fruit 

etc, many columns, "type" column might incorrect.

therefore, correct "type" column different data set has correct type mappings:

table 2, 3000 rows:

food      type cabbage   vegetable carrot    vegetable mango     fruit 

etc.

i trying update "type" data in table 1 "type" table 2.

normally, in matlab or r, use vector notations quite fast. manually, use vlookup() not slow, remove manual process.

i can't seem fast in vba excel. double loops not option, thought i'd use following approach:

worksheets(table1).activate index = 2 lasttype    'for loop goes row 2 last row of table2       'filter rows need         range("a1")         .autofilter field:=1, criteria1:=worksheets(table2).cells(index, 1).value     end       'update value of column 2, table1 corresponding value column 2, table2     range("a2:a" & lastdata).specialcells(xlcelltypevisible).offset(0, 1).value = worksheets(table2).cells(index, 2).value      activesheet.autofiltermode = false next index 

any more efficient approach suggestions appreciated.

as pointed out vlookup not slow. perhaps change formula of each of cells vlookup. code below need modified specific ranges should work nicely.

sub fixtable1()  dim rng excel.range dim row excel.range dim cell excel.range  set rng = worksheets(1).range("a2") lnglastrow = rng.end(xldown).row lastrowtxt = "b" & lnglastrow  set rng = worksheets(1).range("a2:" & lastrowtxt)  = 2 each row in rng.rows      row.cells(1, 2).formula = "=vlookup(a" & & ", table2!$a$2:$b$5, 2, false)"      'first loop b2 = "=vlookup(a2, table2!$a$2:$b$5, 2, false)"     = + 1  next  end sub 

of course based on post change "table2!$a$2:$b$5" "table2!a$2:$b$3001"