xlsx - How can I minimize this code? because excel said it has more than 64 conditions -
note: texts equivalent $c$1 dropdown list.
=if( $c$1="", "", if( $c$1="aglayan 1", t4, if( $c$1="aglayan 2", ai4, if( $c$1="alabel", ax4, if( $c$1="babak 1",bm4,if($c$1="babak 2",cb4,if($c$1="banaybanay",cq4,if($c$1="banga",df4,if($c$1="bansalan",du4,if($c$1="bansalan 2",ej4,if($c$1="bansalan 3",ey4,if($c$1="barobo",fn4,if($c$1="batobato",gc4,if($c$1="bayugan",gr4,if($c$1="buhangin",hg4,if($c$1="calinan",hv4,if($c$1="carmen",ik4,if($c$1="compostela 1",iz4,if($c$1="compostela 2",jo4,if($c$1="cugman",kd4,if($c$1="digos 1",ks4,if($c$1="digos 2",lh4,if($c$1="digos 3",lw4,if($c$1="don carlos 1",ml4,if($c$1="esperanza 1",na4,if($c$1="esperanza 2",np4,if($c$1="esperanza 3",oe4,if($c$1="gensan",ot4,if($c$1="hagonoy",pi4,if($c$1="hinatuan",px4,if($c$1="isulan 1",qm4,if($c$1="kabakan 1",rb4,if($c$1="kabakan 2",rq4,if($c$1="kalilangan",sf4,if($c$1="kapalong",su4,if($c$1="kidapawan 2",tj4,if($c$1="kidapawan 3",ty4,if($c$1="kidapawan 4",un4,if($c$1="kidapawan 5",vc4,if($c$1="kidapawan 6",vr4,if($c$1="kidapawan 7",wg4,if($c$1="kidapawan 8",wv4,if($c$1="krinkles 1",xk4,if($c$1="krinkles 2",xz4,if($c$1="lupon 1",yo4,if($c$1="lupon 2",zd4,if($c$1="maasim",zs4,if($c$1="magpet",aah4,if($c$1="magsaysay",aaw4,if($c$1="maitum",abl4,if($c$1="makilala",aca4,if($c$1="malaybalay",acp4,if($c$1="malita",ade4,if($c$1="mangagoy",adt4,if($c$1="maramag 1",aei4,if($c$1="maramag 2",aex4,if($c$1="marbel 1",afm4,if($c$1="marbel 2",agb4,if($c$1="marikit",agq4,if($c$1="matalam",ahf4,if($c$1="matanao",ahu4,if($c$1="midsayap",aij4,if($c$1="mlang",aiy4,if($c$1="monkayo",ajn4,if($c$1="nabunturan 1",akc4,if($c$1="nabunturan 2",akr4,if($c$1="nabunturan 3",akg4,if($c$1="ozamiz",alv4,if($c$1="padada",amk4,if($c$1="panabo 2",amz4,if($c$1="panabo 3",ano4,if($c$1="panabo 5",aod4,if($c$1="panabo main",aos4,if($c$1="panabo pdp 1",aph4,if($c$1="panabo pdp 2",apw4,if($c$1="pantukan",aql4,if($c$1="peÑaplata",ara4,if($c$1="pigkawayan",arp4,if($c$1="polomolok 1",ase4,if($c$1="polomolok 2",ast4,if($c$1="puerto",ati4,if($c$1="quezon",atx4,if($c$1="san francisco 1",aum4,if($c$1="sta. maria",avb4,if($c$1="sto niÑo",avq4,if($c$1="sto. tomas",awf4,if($c$1="sulop",awu4,if($c$1="surigao 2",axj4,if($c$1="tacorong 1",axy4,if($c$1="tacorong 2",ayn4,if($c$1="tagbina",azc4,if($c$1="tagum 4",azr4,if($c$1="tibungco 1",bag4,if($c$1="tibungco 2",bav4,if($c$1="toril 1",bbk4,if($c$1="toril 2",bbz4,if($c$1="trento",bco4,if($c$1="tulunan",bdd4,if($c$1="tupi",bds4,"")))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))) ) ) ) ) )
please try following...
first, bring worksheet_change event's vba code. click somewhere in woeksheet. choose developer menu. choose design mode. choose view code.
next, paste in following code...
private sub worksheet_change(byval target range) if target.address = "$c$1" select case $c$1 case "aglayan 1" range( "e4:s19" ).value = range( "t4:ah19" ).value case "aglayan 2" range( "e4:s19" ).value = range( "ai4:aw19" ).value case "alabel", range( "e4:s19" ).value = range( "ax4:bl19" ).value case "babak 1", range( "e4:s19" ).value = range( "bm4:ca19" ).value case "babak 2", range( "e4:s19" ).value = range( "cb4:cp19" ).value case "banaybanay", range( "e4:s19" ).value = range( "cq4:de19" ).value case "banga", range( "e4:s19" ).value = range( "df4:dt19" ).value case "bansalan", range( "e4:s19" ).value = range( "du4:ei19" ).value case "bansalan 2", range( "e4:s19" ).value = range( "ej4:ex19" ).value case "bansalan 3", range( "e4:s19" ).value = range( "ey4:fm19" ).value case "barobo", range( "e4:s19" ).value = range( "fn4:gb19" ).value case "batobato", range( "e4:s19" ).value = range( "gc4:gq19" ).value case "bayugan", range( "e4:s19" ).value = range( "gr4:hf19" ).value case "buhangin", hg4, range( "e4:s19" ).value = range( "hg4:hu19" ).value case "calinan", range( "e4:s19" ).value = range( "hv4:ij19" ).value case "carmen", range( "e4:s19" ).value = range( "ik4:iy19" ).value case "compostela 1", range( "e4:s19" ).value = range( "iz4:jn19" ).value case "compostela 2", range( "e4:s19" ).value = range( "jo4:kc19" ).value case "cugman", range( "e4:s19" ).value = range( "kd4:kr19" ).value case "digos 1", range( "e4:s19" ).value = range( "ks4:lg19" ).value case "digos 2", range( "e4:s19" ).value = range( "lh4:lv19" ).value case "digos 3", range( "e4:s19" ).value = range( "lw4:mk19" ).value case "don carlos 1", range( "e4:s19" ).value = range( "ml4:mz19" ).value case "esperanza 1", range( "e4:s19" ).value = range( "na4:no19" ).value case "esperanza 2", range( "e4:s19" ).value = range( "np4:od19" ).value case "esperanza 3", range( "e4:s19" ).value = range( "oe4:os19" ).value case "gensan", range( "e4:s19" ).value = range( "ot4:ph19" ).value case "hagonoy", range( "e4:s19" ).value = range( "pi4:pw19" ).value case "hinatuan", range( "e4:s19" ).value = range( "px4:ql19" ).value case "isulan 1", range( "e4:s19" ).value = range( "qm4:ra19" ).value case "kabakan 1", range( "e4:s19" ).value = range( "rb4:rp19" ).value case "kabakan 2", range( "e4:s19" ).value = range( "rq4:se19" ).value case "kalilangan", range( "e4:s19" ).value = range( "sf4:st19" ).value case "kapalong", range( "e4:s19" ).value = range( "su4:ti19" ).value case "kidapawan 2", range( "e4:s19" ).value = range( "tj4:tx19" ).value case "kidapawan 3", range( "e4:s19" ).value = range( "ty4:um19" ).value case "kidapawan 4", range( "e4:s19" ).value = range( "un4:vb19" ).value case "kidapawan 5", range( "e4:s19" ).value = range( "vc4:vq19" ).value case "kidapawan 6", range( "e4:s19" ).value = range( "vr4:wf19" ).value case "kidapawan 7", range( "e4:s19" ).value = range( "wg4:wu19" ).value case "kidapawan 8", range( "e4:s19" ).value = range( "wv4:xj19" ).value case "krinkles 1", range( "e4:s19" ).value = range( "xk4:xy19" ).value case "krinkles 2", range( "e4:s19" ).value = range( "xz4:yn19" ).value case "lupon 1", range( "e4:s19" ).value = range( "yo4:zc19" ).value case "lupon 2", range( "e4:s19" ).value = range( "zd4:zr19" ).value case "maasim", range( "e4:s19" ).value = range( "zs4:aag19" ).value case "magpet", range( "e4:s19" ).value = range( "aah4:aav19" ).value case "magsaysay", range( "e4:s19" ).value = range( "aaw4:abk19" ).value case "maitum", range( "e4:s19" ).value = range( "abl4:abz19" ).value case "makilala", range( "e4:s19" ).value = range( "aca4:aco19" ).value case "malaybalay", range( "e4:s19" ).value = range( "acp4:add19" ).value case "malita", range( "e4:s19" ).value = range( "ade4:ads19" ).value case "mangagoy", range( "e4:s19" ).value = range( "adt4:aeh19" ).value case "maramag 1", range( "e4:s19" ).value = range( "aei4:aew19" ).value case "maramag 2", range( "e4:s19" ).value = range( "aex4:afl19" ).value case "marbel 1", range( "e4:s19" ).value = range( "afm4:aga19" ).value case "marbel 2", range( "e4:s19" ).value = range( "agb4:agp19" ).value case "marikit", range( "e4:s19" ).value = range( "agq4:ahe19" ).value case "matalam", range( "e4:s19" ).value = range( "ahf4:aht19" ).value case "matanao", range( "e4:s19" ).value = range( "ahu4:aii19" ).value case "midsayap", range( "e4:s19" ).value = range( "aij4:aix19" ).value case "mlang", range( "e4:s19" ).value = range( "aiy4:ajm19" ).value case "monkayo", range( "e4:s19" ).value = range( "ajn4:akb19" ).value case "nabunturan 1", range( "e4:s19" ).value = range( "akc4:akq19" ).value case "nabunturan 2", range( "e4:s19" ).value = range( "akr4:alf19" ).value case "nabunturan 3", range( "e4:s19" ).value = range( "alg4:alu19" ).value case "ozamiz", range( "e4:s19" ).value = range( "alv4:amj19" ).value case "padada", range( "e4:s19" ).value = range( "amk4:amy19" ).value case "panabo 2", range( "e4:s19" ).value = range( "amz4:annp19" ).value case "panabo 3", range( "e4:s19" ).value = range( "ano4:aoc19" ).value case "panabo 5", range( "e4:s19" ).value = range( "aod4:aor19" ).value case "panabo main", range( "e4:s19" ).value = range( "aos4:apg19" ).value case "panabo pdp 1", range( "e4:s19" ).value = range( "aph4:apv19" ).value case "panabo pdp 2", range( "e4:s19" ).value = range( "apw4:aqk19" ).value case "pantukan", range( "e4:s19" ).value = range( "aql4:aqz19" ).value case "peÑaplata", range( "e4:s19" ).value = range( "ara4:aro19" ).value case "pigkawayan", range( "e4:s19" ).value = range( "arp4:asd19" ).value case "polomolok 1", range( "e4:s19" ).value = range( "ase4:ass19" ).value case "polomolok 2", range( "e4:s19" ).value = range( "ast4:ath19" ).value case "puerto", range( "e4:s19" ).value = range( "ati4:atw19" ).value case "quezon", range( "e4:s19" ).value = range( "atx4:aul19" ).value case "san francisco 1", range( "e4:s19" ).value = range( "aum4:ava19" ).value case "sta. maria", range( "e4:s19" ).value = range( "avb4:avp19" ).value case "sto niÑo", range( "e4:s19" ).value = range( "avq4:awe19" ).value case "sto. tomas", range( "e4:s19" ).value = range( "awf4:awt19" ).value case "sulop", range( "e4:s19" ).value = range( "awu4:axi19" ).value case "surigao 2", range( "e4:s19" ).value = range( "axj4:axx19" ).value case "tacorong 1", range( "e4:s19" ).value = range( "axy4:aym19" ).value case "tacorong 2", range( "e4:s19" ).value = range( "ayn4:azb19" ).value case "tagbina", range( "e4:s19" ).value = range( "azc4:azq19" ).value case "tagum 4", range( "e4:s19" ).value = range( "azr4:baf19" ).value case "tibungco 1", range( "e4:s19" ).value = range( "bag4:bau19" ).value case "tibungco 2", range( "e4:s19" ).value = range( "bav4:bbj19" ).value case "toril 1", range( "e4:s19" ).value = range( "bbk4:bby19" ).value case "toril 2", range( "e4:s19" ).value = range( "bbz4:bcn19" ).value case "trento", range( "e4:s19" ).value = range( "bco4:bdc19" ).value case "tulunan", range( "e4:s19" ).value = range( "bdd4:bdr19" ).value case "tupi", range( "e4:s19" ).value = range( "bds4:beg19" ).value case else range( "e4" ).value = "error encountered" end if end sub finally, choose close , return microsoft excel file menu.
if have questions or comments, please feel free post comment accordingly.
Comments
Post a Comment