sorting - Custom Sort using vba code -

-

for following piece of code, getting runtime error '13', type mismatch error when reaches following piece of code

activeworkbook.worksheets("3. pmo internal view").sort.sortfields.add key:= _ f, sorton:=xlsortonvalues, order:=xlascending, dataoption:= _ xlsortnormal

that piece of code above in full code below, have placed in bold, towards end of code.

what trying filter current state column (which works fine), want custom sort 2nd , 3rd columns ("pcr no." , "accn. id" respectively). work fine if used original recorded code (range("b2:b2000"), sorton:=xlsortonvalues, order:=xlascending, dataoption:=) thing want ensure macro not break if decided column later @ beginning trying custom sort column name not column number.

any appreciated here.

sub commercialview() ' ' commercialview macro '    ' dim wrkbk, sourcebk workbook set sourcebk = application.activeworkbook 'clear filter columns start activesheet if .autofiltermode if .filtermode .showalldata end if else if .filtermode .showalldata end if end if end 'clear filter columns end    'copy required columns , add them destination spreadsheet start   workbooks.add   set wrkbk = application.activeworkbook   sourcebk.activate   wrkbk.activate   sourcebk.activate    dim acell1, acell2, acell3, acell4, acell5, acell6, acell7, acell8, acell9, acell10, acell11, acell12 range   dim strsearch1, strsearch2, strsearch3, strsearch4, strsearch5, strsearch6, strsearch7, strsearch8, strsearch9, strsearch10, strsearch11, strsearch12 string    strsearch1 = "change request description"   strsearch2 = "pcr no."   strsearch3 = "accn. id"   strsearch4 = "current state"   strsearch5 = "approved date"   strsearch6 = "project"   strsearch7 = "planned commencement date"   strsearch8 = "notes"   strsearch9 = "total price (iia, dia, execution ($)"   strsearch10 = "price calculator status"   strsearch11 = "om entry"   strsearch12 = "cvp ref. no."    set acell1 = sheets("3. pmo internal view").rows(1).find(what:=strsearch1, lookin:=xlvalues, _       lookat:=xlwhole, searchorder:=xlbyrows, searchdirection:=xlnext, _       matchcase:=false, searchformat:=false)    set acell2 = sheets("3. pmo internal view").rows(1).find(what:=strsearch2, lookin:=xlvalues, _       lookat:=xlwhole, searchorder:=xlbyrows, searchdirection:=xlnext, _       matchcase:=false, searchformat:=false)    set acell3 = sheets("3. pmo internal view").rows(1).find(what:=strsearch3, lookin:=xlvalues, _       lookat:=xlwhole, searchorder:=xlbyrows, searchdirection:=xlnext, _       matchcase:=false, searchformat:=false)    set acell4 = sheets("3. pmo internal view").rows(1).find(what:=strsearch4, lookin:=xlvalues, _       lookat:=xlwhole, searchorder:=xlbyrows, searchdirection:=xlnext, _       matchcase:=false, searchformat:=false)    set acell5 = sheets("3. pmo internal view").rows(1).find(what:=strsearch5, lookin:=xlvalues, _       lookat:=xlwhole, searchorder:=xlbyrows, searchdirection:=xlnext, _       matchcase:=false, searchformat:=false)    set acell6 = sheets("3. pmo internal view").rows(1).find(what:=strsearch6, lookin:=xlvalues, _       lookat:=xlwhole, searchorder:=xlbyrows, searchdirection:=xlnext, _       matchcase:=false, searchformat:=false)    set acell7 = sheets("3. pmo internal view").rows(1).find(what:=strsearch7, lookin:=xlvalues, _       lookat:=xlwhole, searchorder:=xlbyrows, searchdirection:=xlnext, _       matchcase:=false, searchformat:=false)    set acell8 = sheets("3. pmo internal view").rows(1).find(what:=strsearch8, lookin:=xlvalues, _       lookat:=xlwhole, searchorder:=xlbyrows, searchdirection:=xlnext, _       matchcase:=false, searchformat:=false)    set acell9 = sheets("3. pmo internal view").rows(1).find(what:=strsearch9, lookin:=xlvalues, _       lookat:=xlwhole, searchorder:=xlbyrows, searchdirection:=xlnext, _       matchcase:=false, searchformat:=false)    set acell10 = sheets("3. pmo internal view").rows(1).find(what:=strsearch10, lookin:=xlvalues, _       lookat:=xlwhole, searchorder:=xlbyrows, searchdirection:=xlnext, _       matchcase:=false, searchformat:=false)    set acell11 = sheets("3. pmo internal view").rows(1).find(what:=strsearch11, lookin:=xlvalues, _       lookat:=xlwhole, searchorder:=xlbyrows, searchdirection:=xlnext, _       matchcase:=false, searchformat:=false)    set acell12 = sheets("3. pmo internal view").rows(1).find(what:=strsearch12, lookin:=xlvalues, _       lookat:=xlwhole, searchorder:=xlbyrows, searchdirection:=xlnext, _       matchcase:=false, searchformat:=false)    '~~> copying here    sheets("3. pmo internal view").range(sheets("3. pmo internal view").columns(acell1.column).address & "," _         & sheets("3. pmo internal view").columns(acell2.column).address & "," _         & sheets("3. pmo internal view").columns(acell3.column).address & "," _         & sheets("3. pmo internal view").columns(acell4.column).address & "," _         & sheets("3. pmo internal view").columns(acell5.column).address & "," _         & sheets("3. pmo internal view").columns(acell6.column).address & "," _         & sheets("3. pmo internal view").columns(acell7.column).address & "," _         & sheets("3. pmo internal view").columns(acell8.column).address & "," _         & sheets("3. pmo internal view").columns(acell9.column).address & "," _         & sheets("3. pmo internal view").columns(acell10.column).address & "," _         & sheets("3. pmo internal view").columns(acell11.column).address & "," _         & sheets("3. pmo internal view").columns(acell12.column).address).copy    'range("a1,b1,c1,d1,e1,g1,h1,i1,r1,v1,w1,x1").entirecolumn.select   'selection.copy    range("a2").select   wrkbk.activate   activesheet.paste   selection.autofilter   'copy required columns , add them destination spreadsheet end    'to remove data validation start   cells.select   selection.validation       .delete       .add type:=xlvalidateinputonly, alertstyle:=xlvalidalertstop, operator _       :=xlbetween       .ignoreblank = true       .incelldropdown = true       .inputtitle = ""       .errortitle = ""       .inputmessage = ""       .errormessage = ""       .showinput = true       .showerror = true   end   'to remove data validation end    wrkbk.activate   wrkbk.sheets("sheet1").select    'filter column price calculator status require review pricing start    dim p integer, rngdata range   set rngdata = range("a1").currentregion   p = application.worksheetfunction.match("price calculator status", range("a1:az1"), 0)    rngdata.autofilter field:=p, criteria1:="=completed - requires review pricing"    'filter column price calculator status require review pricing end    'copy status definitions tab new worksheet start    sourcebk.sheets("2. status definitions").copy _   after:=activeworkbook.sheets("sheet1")    'copy status definitions tab new worksheet end    wrkbk.sheets("sheet1").select   range("a5").select    'save desktop directory dod folder name - compatible user runs macro start    dim uname string: uname = environ("username")    fpath1 = "c:\users\" & uname & "\desktop\dod"   fpath2 = "c:\users\" & uname & "\desktop\dod\change status request report"   fpath3 = "c:\users\" & uname & "\desktop\dod\change status request report\commercial view"    if dir(fpath1, vbdirectory) = vbnullstring mkdir fpath1   if dir(fpath2, vbdirectory) = vbnullstring mkdir fpath2   if dir(fpath3, vbdirectory) = vbnullstring mkdir fpath3   activeworkbook.saveas (fpath3 & "\internal change status request report - commercial view - " & format(now, "yyyy-mm-dd"))   activeworkbook.close    'save desktop directory dod folder name - compatible user runs macro end    'return overall cr tracker , filter out approved , cancelled crs start    dim s, f, g integer, rngdata2, rngdata5, rngdata6 range   set rngdata2 = range("a1").currentregion   s = application.worksheetfunction.match("current state", range("a1:az1"), 0)    rngdata2.autofilter field:=s, criteria1:=array( _   "detailed impact assessment", "draft – yet tabled @ cccm", _   "initial impact assessment", "new", "on hold", "pending approval - execution", _   "pending approval - iia"), operator:=xlfiltervalues    set rngdata5 = range("b1").currentregion   f = application.worksheetfunction.match("pcr no.", range("a1:az1"), 0)   set rngdata6 = range("c1").currentregion   g = application.worksheetfunction.match("accn. id", range("a1:az1"), 0)    activeworkbook.worksheets("3. pmo internal view").sort.sortfields.clear   **activeworkbook.worksheets("3. pmo internal view").sort.sortfields.add key:= _   f, sorton:=xlsortonvalues, order:=xlascending, dataoption:= _   xlsortnormal**   activeworkbook.worksheets("3. pmo internal view").sort.sortfields.add key:= _   g, sorton:=xlsortonvalues, order:=xlascending, dataoption:= _   xlsortnormal   activeworkbook.worksheets("3. pmo internal view").sort     .setrange range("a1:x2000")     .header = xlyes     .matchcase = false     .orientation = xltoptobottom     .sortmethod = xlpinyin     .apply   end    'return overall cr tracker , filter out approved , cancelled crs end end sub

the key parameter of sort should have range or cell address. f defined f = application.worksheetfunction.match() returns number.

you should have set f = range("a1") or f = "a1". excel use column contains specified cell.

edit 1:

instead of:

f = application.worksheetfunction.match("pcr no.", range("a1:az1"), 0) activeworkbook.worksheets("3. pmo internal view").sort.sortfields.add key:= f

you should use:

f = application.worksheetfunction.match("pcr no.", range("a1:az1"), 0) activeworkbook.worksheets("3. pmo internal view").sort.sortfields.add key:= cells(1, f)

some other comments come mind looking @ code:

you variable declarations don't think:

dim s, f, g integer, rngdata2, rngdata5, rngdata6 range 'is equivalent dim s variant, f variant, g integer, rngdata2 variant, rngdata5 variant, rngdata6 range  'you should write dim s integer, f integer, g integer, rngdata2 range, rngdata5 range, rngdata6 range

you paste code. when saw it, skimmed through , lucky saw problem. skip questions this. should try write function short possible reproduces same problem. helps 2 reasons: it's more read , give solution, , it's more figure out on own during process of reducing problem. start writing questions here, don't post them because thinking how write can understand it, makes me understand it.

mark answers answers. noticed never that, , many people others in exchange little gratifying check mark. if guy doesn't gratify, people don't you.

edit 2:

i think need (this time tested it):

  dim f integer, g integer, sh worksheet   set sh = sheets("3. pmo internal view")   f = worksheetfunction.match("pcr no.", sh.range("a1:az1"), 0)   g = worksheetfunction.match("accn. id", sh.range("a1:az1"), 0)   sh.range("a1:x2000").sort key1:=sh.cells(1, f), order1:=xlascending, key2:=sh.cells(1, g), order2:=xlascending

notice use sh sheet specify sheet range , cells properties refer to. allows use function regardless of active sheet. using cells(...) or range(...) without specifying sheet defaults active sheet, , forces activate sheet want sort before sorting it.


Comments

Post a Comment

Popular posts from this blog

css - Which browser returns the correct result for getBoundingClientRect of an SVG element? -

-
this svg contains rect overflows svg element: <svg id='svg' width='10' height='10'> <rect x='-10' y='-10' width='30' height='30'/> </svg> chrome 28 , opera 12 return getboundingclientrect() svg element width , height of 10. firefox 23 reports width , height of 30. correct? jsfiddle the relevant spec cssom , delegates svg spec if svg element not "have associated css layout box". haven't found definition of "having associated css layout box", correct result seem hinge on definition, getbbox returns 30x30 rect in browsers. this firefox bug fixed now, fix in firefox nightlies , should make through firefox 33 released on 14 october 2014. see bug 530985 details.
Read more

gcc - Calling fftR4() in c from assembly -

-
i trying asm fft implementation www.embeddedsignals.com work. running make gives error: undefined reference fftr4(short*, short*, int) at top of asm have .syntax unified .thumb .global fftr4 .type fftr4, %function and there #define's associate c functions arguments registers followed macros fftr4 function uses. after that, fftr4 called .thumb_func fftr4: stmfd sp!, {r4-r11, lr} mov tmp0, #0 // bit reversed counter mov tmp1, #0 .word 0xf3a2fa92 //rbit r3,r2 //rbit r,n lsl r,#3 and c code follows: void fftr4(short *y, short *x, int n); short x[512]; short y[512]; int i; void loop() { (i=0;i<512;i++) x[i]=0; (i=0;i<512;i=i+8) { x[i+0]=16384; x[i+2]=16384; x[i+4]=-16384; x[i+6]=-16384;} fftr4(y, x, 256); serialusb.println(y[128]); serialusb.println(y[129]); serialusb.println(y[384]); serialusb.println(y[385]); // expected results: //y[128]is 8191; y[129] -8190 y[...
Read more

.htaccess - Matching full URL in RewriteCond -

-
i'm trying block post access site (and return 403), except 2 ip's , 1 url ( http://mydomain.com/about-us/contact-us/ ). here's have: rewritecond %{request_method} post rewritecond %{remote_addr} !=1.2.3.4 rewritecond %{remote_addr} !=4.3.2.1 rewritecond %{request_uri} !^http://mydomain.com/about-us/contact-us/$ rewriterule ^ / [f] these rules allowing access ip properly, contact page still not accessible. i'm not sure i'm using request_uri correctly? i've tried variations relative urls rewritecond %{request_uri} !^/about-us/contact-us$ , rewritecond %{request_uri} !^/about-us/contact-us/$ without luck. what doing wrong please? it looks me rule on file name specific. if relax bit, should work. made few other esthetic tweaks. give try: rewritecond %{request_method} post rewritecond %{remote_addr} !^1.2.3.4 rewritecond %{remote_addr} !^4.3.2.1 rewritecond %{request_uri} !about-us/contact-us rewriterule ^ - [f]
Read more