vba to set up a dynamic range based on two column -

-

i trying set dynamic range based on 2 column, criteria is...

  1. i have 2 columns called "name" , "phone"
  2. column "name" in left side of "phone"

range wish is,

  1. find column "phone"
  2. set range in column "phone" 1st non-empty row , last row in column "name"

so far able find column "phone" , remaining stuff not able relate range column "name", please see below macro , suggest how should able it.

hey in advance...

    option explicit  sub rulepicker()     dim ws worksheet     dim acell range, rng range     dim col long, lrow long     dim colname string      '~~> change relevant sheet     set ws = thisworkbook.sheets("sepy build")      ws         set acell = .range("a1:zz1").find(what:="phone", lookin:=xlvalues, lookat:=xlwhole, _                     matchcase:=false, searchformat:=false)          '~~> if found         if not acell nothing             col = acell.column             colname = split(.cells(, col).address, "$")(1)              lrow = .range(colname & .rows.count).end(xlup).row              '~~> range             set rng = .range(colname & "13:" & colname & lrow)                 rng.formula = "=if(rc[-9]="""","""",if(left(lower(rc[-9]),4)=""none"","""",vlookup(left(rc[-9],(find("" "",rc[-9],1)-1)),'base rule'!c[-10]:c[-5],2,0)))"                 rng.value = rng.value                 rng.offset(0, 1).formular1c1 = "=if(rc[-1]="""","""",vlookup(rc[-1],rules!c[-12]:c[-11],2,0))"                 rng.offset(0, 2).formular1c1 = "=if(rc[-11]="""","""",if(left(lower(rc[-11]),4)=""none"","""",vlookup(left(rc[-11],(find("" "",rc[-11],1)-1)),'base rule'!c[-12]:c[-7],3,0)))"                 rng.offset(0, 2).value = rng.offset(0, 2).value                 rng.offset(0, 3).formular1c1 = "=if(rc[-12]="""","""",if(left(lower(rc[-12]),4)=""none"","""",vlookup(left(rc[-12],(find("" "",rc[-12],1)-1)),'base rule'!c[-13]:c[-8],4,0)))"                 rng.offset(0, 3).value = rng.offset(0, 3).value                 rng.offset(0, 4).formular1c1 = "=if(rc[-1]="""","""",vlookup(rc[-1],rules!c[-15]:c[-14],2,0))"                 rng.offset(0, 5).formular1c1 = "=if(rc[-14]="""","""",if(left(lower(rc[-14]),4)=""none"","""",vlookup(left(rc[-14],(find("" "",rc[-14],1)-1)),'base rule'!c[-15]:c[-10],5,0)))"                 rng.offset(0, 5).value = rng.offset(0, 5).value             debug.print rng.address         '~~> if not found         else             msgbox "phone header not found"         end if     end end sub


Comments

Post a Comment

Popular posts from this blog

c++ - Delete matches in OpenCV (Keypoints and descriptors) -

-
i want check scene image against 2 train images. that, detect features , compute descriptors of both training images. before detecting, computing , matching scene image, delete matches of train1 , train2. because these matches won't facilitate matching of scene image train1 , train2. so, match train1 train2 , vector of matches trainidx , queryidx. how can delete these matches in keypoints-vector , descriptor matrix of train1 , train2? best regards, dwi i have done below: std::vector<cv::keypoint> keypoints[2]; cv::mat descriptor[2]; std::vector< cv::dmatch > matches; /* write code generate keypoints, descriptors , matches here... keypoint[0] -> train image 1 keypoints keypoint[1] -> train image 2 keypoints descriptor[0] -> train image 1 descriptors descriptor[1] -> train image 2 descriptors matches -> matched between train image 1 , 2 */ // logic keep unmatched keypoints , corresponding descriptor
Read more

java - Could not locate OpenAL library -

-
i writing game using slick2d library, uses lwjgl. lwjgl version 2.9.3. using netbeans 8. i tried adding sounds game, example music track on main menu. problem is, exception when run game, despite game still compiling , running except sound of course: sat mar 21 01:23:57 edt 2015 error:could not locate openal library. org.lwjgl.lwjglexception: not locate openal library. @ org.lwjgl.openal.al.create(al.java:156) @ org.lwjgl.openal.al.create(al.java:156) @ org.lwjgl.openal.al.create(al.java:102) @ org.lwjgl.openal.al.create(al.java:206) @ org.newdawn.slick.openal.soundstore$1.run(soundstore.java:295) @ java.security.accesscontroller.doprivileged(native method) @ org.newdawn.slick.openal.soundstore.init(soundstore.java:292) @ org.newdawn.slick.music.<init>(music.java:156) @ org.newdawn.slick.music.<init>(music.java:75) @ view.mainmenu.init(mainmenu.java:58) i didn't include rest of error states error happens in game code. the
Read more

sorting - opencl Bitonic sort with 64 bits keys -

-
i used nvidia sdk bitonic sort , works great me. 32 bits (uint) need ulong keys. have typically 2^14 keys @ time , power of 2. searched on not find kernel designed ulong. i tried modified nvidia sdk bitonic sort using ulong keys not work. kernel not crash after de call clenqueuendrangekernel got error : cl_invalid_command_queue can tell me how modify bitonic sort or radixsort, or can sort ulong keys? i running nvidia quadro 4000 opencl 1.1 cuda 6.5.20 full_profile used original nvidia sdk bitonicsort.cl i used"ulong" keys , value input , ouput instead of "uint" thanks helping i made work-around suit need. used 30 bits of key , 18 high bits of value sorting. changed comparators (2 lines): '#define mask 0xffffc000 // suit need from this: if( (*keya > *keyb) == dir ) this: if(((*keya > *keyb)||((*keya == *keyb)&&((*vala & mask)> (*valb & mask)))) == dir ) it works fine. but, still, it's work-a
Read more