excel vba - vlookup splitted through items does not work -

-

one time, asked on function vlookup split value. using long time. now, code no longer seems work. can reason code working no longer does? 

sub test() dim cl range, key variant dim dic object: set dic = createobject("scripting.dictionary") dic.comparemode = vbtextcompare sheets("sheet1")     each cl in .range("a1:a" & .cells.specialcells(xlcelltypelastcell).row)         if cl.value <> ""             dic.add cl.row & "|" & replace(lcase(cl.value), ";", "||") & "|", cl.offset(, 1).text         end if     next cl end with sheets("sheet2")     each cl in .range("a1:a" & .cells.specialcells(xlcelltypelastcell).row)         each key in dic             if key "*|" & lcase(cl.value) & "|*" , cl.value <> ""                 cl.offset(, 1).value = dic(key)                 exit             end if         next key     next cl end end sub

at moment there no errors code not working. people working. me not. please see expected result below:

enter image description here

i felt need refactor code because posted answer on how wildcard search of dictionary.

could edit answer include sample data column of sheet1 & sheet2? 

sub refactoredcode()      dim cl range     dim key, keys, results     dim matchstring string      dim dic object: set dic = createobject("scripting.dictionary")     dic.comparemode = vbtextcompare      sheets("sheet1")         each cl in .range("a1", .range("a" & rows.count).end(xlup)).specialcells(xlcelltypevisible)              dic.add cl.row & "|" & replace(lcase(cl.value), ";", "||") & "|", cl.offset(, 1).text          next cl     end      keys = dic.keys      sheets("sheet2")         each cl in .range("a1", .range("a" & rows.count).end(xlup)).specialcells(xlcelltypevisible)          matchstring = "|" & lcase(cl.value) & "|"             results = filter(keys, matchstring, true, vbtextcompare)              if ubound(results) > -1                 key = results(0)                 cl.offset(, 1).value = dic(key)             end if          next cl     end  end sub

i ran both refactoredcode() , test(). both of them working properly.

enter image description here


Comments

Post a Comment

Popular posts from this blog

magento2 - Magento 2 admin grid add filter to collection -

-
i created grid in admin xml ui components. need filter collection via url parameter , dont know how achieve that. tried inject requestinterface collection, filter didnt work. di.xml <virtualtype name="slidelistingdataprovider" type="magento\framework\view\element\uicomponent\dataprovider\dataprovider"> <arguments> <argument name="collection" xsi:type="object" shared="false">xxx\xxx\model\resourcemodel\grid\slide\collection</argument> <argument name="filterpool" xsi:type="object" shared="false">slidelistingfilterpool</argument> <!-- define new object filters --> </arguments> </virtualtype> <virtualtype name="slidelistingfilterpool" type="magento\framework\view\element\uicomponent\dataprovider\filterpool"> <arguments> <argument name="applier...
Read more

Android volley - avoid multiple requests of the same kind to the server? -

-
i using volley upload list of images server. happens within service. once image uploaded remove list. problem arises when internet connection breaks. when internet connection breaks , comes again thinking of adding images yet uploaded request queue. this can result in more 1 copy of same image saved on server request same image might have gone through before getting rseponse. how can address scenario? i'm not quite sure why you're using service schedule requests volley automatically runs it's request in seperate thread. nevertheless can listen both request results, successful , unsuccessful adding listeners. you can remove image list schedule request using volley. if succeeds can carry on desired, if fails can add again list. public static request getimageuploadrequest(final string image) { response.listener<t> responselistener = new response.listener<t>() { @override public void onresponse(t response...
Read more

Combining PHP Registration and Login into one class with multiple functions in one PHP file -

-
i'm trying combine registration, activation , login scripts php website backend script front end developer can pass variables different forms to. my question whether appropriate approach this. don't want have lot of php files different pieces of application developing. far have written following 2 functions login, register , activate user front end developer can call: <?php /** * created phpstorm. * user: karl * date: 26/07/2016 * time: 02:25 */ class users { function register_user($email, $password, $user_name) { $server_name = "localhost"; $u_name = "root"; $db_password = "root"; $db_name = "betamath_graspe"; //email notification variable $from_address="info@slack.com"; //registration form $msg_reg_user='username taken. please choose different username'; $msg_reg_email='email registered'; $msg_reg_active=...
Read more