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

Lists in Python -

-
i noticed can multiply list scalar behavior weird if multiply: [2,3,4] * 3 i get: [2,3,4,2,3,4,2,3,4 ] understand results it's for? there other weird operations that? the main purpose of operand initialisation. example, if want initialize list 20 equal numbers can using loop: arr=[] in range(20): arr.append(3) an alternative way using operator: arr = [3] * 20 more weird , normal list operation on lists can find here http://www.discoversdk.com/knowledge-base/using-lists-in-python
Read more

android - can not access to progress bar in an other activity -

-
i have objects in layout video view,text view , on comes this: <?xml version="1.0" encoding="utf-8"?> <android.support.v7.widget.cardview android:id="@+id/cardview_mypost" android:layout_width="match_parent" android:layout_height="wrap_content" android:layout_marginbottom="@dimen/activity_vertical_margin" app:cardcornerradius="@dimen/activity_vertical_margin" app:cardelevation="@dimen/activity_vertical_margin" xmlns:android="http://schemas.android.com/apk/res/android" xmlns:app="http://schemas.android.com/apk/res-auto"> <relativelayout xmlns:android="http://schemas.android.com/apk/res/android" android:layout_width="fill_parent" android:layout_height="wrap_content" android:background="@color/web_withe_color" android:animatelayoutchanges="@anim/anim_list" android:padding="8dp" > <!-- thu...
Read more

html - Not able to access next element of an array javascript -

-
i new js, trying access next element of array using onclick function no luck. var i, len; function quiz() { var quiz_questions = [ "who founder of facebook?", "who founder of google?" ]; len = quiz_questions.length; for(i = 0; < len; i++) { document.getelementbyid("demo").innerhtml = quiz_questions[i]; } } <button onclick="quiz();">click</button> <p id="demo"></p> can explain me did wrong here. your problem is, iterate through every question, every time click. remove loop , put i++ after setting question. maybe checking, if less array length before it, wouldnt bad idea. var i, len; = 0; function quiz() { var quiz_questions = [ "who founder of facebook?", "who founder of google?" ]; len = quiz_questions.length; document.gete...
Read more