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.