VBA script for sorting through Excel data is causing a "Not Responding" -
i'm new vba. works if keep i<=20
need increase i<2000
. increasing 50 causes not responding response.
the code sorts through list of words ("list") , find them in large array called "library." "library" contains multiple columns numbers on each header. want go down list, , insert number columns each entry occur in throughout library.
sub listlibrarycompare() dim rng range dim keyword range dim chunk range dim library worksheet dim list worksheet dim bottom range dim long dim x long = 1 x = 0 while <= 20 set library = sheets("library") set list = sheets("list") set keyword = list.range("a" & i) set bottom = library.range("a150").offset(0, x) set rng = library.range("a1").offset(0, x) set chunk = library.range(rng, bottom) application.screenupdating = false if isempty(rng.value) = true = + 1 x = 0 elseif application.worksheetfunction.countif(chunk, keyword) = 0 x = x + 1 elseif application.worksheetfunction.countif(chunk, keyword) = 1 rng.copy keyword.offset(0, 1).insert shift:=xltoright x = x + 1 end if wend end sub
any feedback appreciated. starting point new approach welcomed.
Comments
Post a Comment