excel vba - Vlookup Function in vba having repeated task -


i found lookup vba google , did modification. however, have hard time figure out way resolve issue of duplicating coding same task (explain below) objective 1 value , return multiples value @ 1 time. below of steps:

  1. the raw data table (data analysis) c8 o399

  2. the lookup value a5 a172 , return result placed @ t5 t172 (at sheet name "graph")

  3. then repeat again next duplicating test code @ step 1 , 2 slight different column

  4. i definite again raw data table (data analysis) c8 i399 - *different column 1st step

  5. the lookup value a5 a172 , return result placed @ v5 v172 ( @ sheet name "graph")

  6. i repeat again 1 , 2 until finish lookup multiple return (approximately around 15 values)

so having hard time put loop value of column , table changing every lookup.

two columns of lookup table raw data allow every lookup task (this different vkloop need specific column for) point add raw data go few thousand line , seeing code using "collection" storeage. not sure for.

below coding

sub vlookupvalues(lookupcategory range, lookupvalues range, vlookupcol object)     dim long, resarr() variant     redim resarr(lookupcategory.rows.count, 1)     = 1 lookupcategory.rows.count         resarr(i - 1, 0) = vlookupcol.item(cstr(lookupcategory(i)))     next     lookupvalues = resarr end sub

any helps appreciated. thanks.

sub testvba() optimizevba true dim starttime single, endtime single starttime = timer  dim testnames range, testvalues range dim lookuptestnames range, lookuptestvalues range dim vlookupcol object    set testnames = worksheets("data analysis").range("c8:c399") set testvalues = worksheets("data analysis").range("o8:o399")    set lookuptestnames = worksheets("graph").range("a5:a172") set lookuptestvalues = worksheets("graph").range("t5:t172")  'set testvalues = worksheets("data analysis").range("i8:i" & 399) 'set lookuptestnames = worksheets("graph").range("a5:a172") 'set lookuptestvalues = worksheets("graph").range("u5:u172")  'build collection set vlookupcol = buildlookupcollection(testnames, testvalues)  'lookup values 'vlookupvalues lookuptestnames, lookuptestvalues, vlookupcol vlookupvalues lookuptestnames, lookuptestvalues, vlookupcol endtime = timer debug.print (endtime - starttime) & " seconds have passed [vba]" optimizevba false set vlookupcol = nothing  'for lower test spec**************************************************************** set testnames = worksheets("data analysis").range("c8:c" & 399) set testvalues = worksheets("data analysis").range("i8:i" & 399) set lookuptestnames = worksheets("graph").range("a5:a172") set lookuptestvalues = worksheets("graph").range("u5:u172")  'build collection set vlookupcol = buildlookupcollection(testnames, testvalues)  'lookup values vlookupvalues lookuptestnames, lookuptestvalues, vlookupcol endtime = timer debug.print (endtime - starttime) & " seconds have passed [vba]" optimizevba false set vlookupcol = nothing  'for upper test spec****************************************************************  set testnames = worksheets("data analysis").range("c8:c" & 399) set testvalues = worksheets("data analysis").range("j8:j" & 399) set lookuptestnames = worksheets("graph").range("a5:a172") set lookuptestvalues = worksheets("graph").range("v5:v172")  'build collection set vlookupcol = buildlookupcollection(testnames, testvalues)  'lookup values vlookupvalues lookuptestnames, lookuptestvalues, vlookupcol endtime = timer debug.print (endtime - starttime) & " seconds have passed [vba]" optimizevba false set vlookupcol = nothing

ali has right approach. lookup array column pairs make looping easier.

sub testvba2()     optimizevba true     dim starttime single     dim cset, vlookupcol object      each cset in array(array("i", "t"), array("j", "u"), array("o", "v"))         starttime = timer          worksheets("data analysis").rows("8:399")             set vlookupcol = buildlookupcollection(.columns("c"), .columns(cset(0)))         end          worksheets("graph").rows("5:172")             vlookupvalues .columns("a"), .columns(cset(1)), vlookupcol         end          debug.print timer - starttime, "data analysis column:"; cset(0), "graph column:"; cset(1)     next      optimizevba false  end sub

-

Comments

Post a Comment

Popular posts from this blog

amazon web services - S3 Pre-signed POST validate file type? -

i know it's possible put limit on file size content-length-range header. possible validate file type? https://docs.aws.amazon.com/amazons3/latest/dev/httppostforms.html#policyconditions i see there content-type header, if set say, audio/mp3 allow mp3 files , return error if file not mp3? i found previous question answers mention validating file size: s3 direct upload restricting file size , type you can specify content-type @ post request. you can specify @ signature, post must done content-type: { "expiration": "2007-12-01t12:00:00.000z", "conditions": [ {"acl": "public-read" }, {"bucket": "johnsmith" }, {"content-type: "audio/mp3"} ] } creating html form (using aws signature version 4) edit: @ previous question found, checking content-type.
Read more

c# - Check Keyboard Input Winforms -

i wondering if instead of doing this protected override void onkeydown(keyeventargs e) { if (e.keycode == keys.a) console.writeline("the key down."); } i set bool method , this: if(keydown(keys.a)) // whatever here i've been sitting here ages trying figure out how it. can't wrap head around it. in case wondering, plan call bool inside different method, check input. since want perform action after pressing key, using keydown event enough. but in cases suppose want check if specific key down in middle of process, can use getkeystate method way: [dllimport("user32.dll", charset = charset.auto, exactspelling = true)] public static extern short getkeystate(int keycode); public const int key_pressed = 0x8000; public static bool iskeydown(keys key) { return convert.toboolean(getkeystate((int)key) & key_pressed); } you should know, each time check key state using example iskeydown(keys.a) method returns true if ke...
Read more