excel - Autofilter Run-time error '91' VBA -


i need help.

i autofilter column of table following value: begins af. , copy , paste column sheet.

i have written code alwasy error when code reach following line:

.autofilter field:=rng0.column, criteria1:=searchfor

the error is: object variable or block not set.

i have no idea wrong code. please me. 

sub af_update()  application.screenupdating = false application.displayalerts = false application.enableevents = false  searchcol0 = "prefix+short name" searchcol1 = "site type" searchcol2 = "sla target" searchcol3 = "mean rtt (ms)" searchcol4 = "max rtt (ms)" searchcol5 = "threshold 95%" searchcol6 = "threshold 99%" searchfor = "=af*"  dim rng0, rng1, rng2, rng3, rng4, rng5, rng6 range dim lastrow long  set rng0 = activesheet.usedrange.find(searchcol0, , xlvalues, xlwhole) set rng1 = activesheet.usedrange.find(searchcol1, , xlvalues, xlwhole) set rng2 = activesheet.usedrange.find(searchcol2, , xlvalues, xlwhole) set rng3 = activesheet.usedrange.find(searchcol3, , xlvalues, xlwhole) set rng4 = activesheet.usedrange.find(searchcol4, , xlvalues, xlwhole) set rng5 = activesheet.usedrange.find(searchcol5, , xlvalues, xlwhole) set rng6 = activesheet.usedrange.find(searchcol6, , xlvalues, xlwhole)    set target = thisworkbook.worksheets("af") set source = thisworkbook.worksheets("raw data")  target.select  range("a2").select range(activecell, cells(activecell.end(xldown).row, activecell.end(xltoright).column)).select selection.clearcontents      source.select      if activesheet.autofiltermode = true         range("a1").autofilter     end if      range("a1").select     selection     .autofilter field:=rng0.column, criteria1:=searchfor     end       rng0.offset(1, 0).select     range(selection, selection.end(xldown)).copy     target.select     range("a2").select     selection.pastespecial paste:=xlpastevalues, operation:=xlnone, skipblanks _         :=false, transpose:=false      source.select     rng1.offset(1, 0).select     range(selection, selection.end(xldown)).copy     target.select     range("b2").select     selection.pastespecial paste:=xlpastevalues, operation:=xlnone, skipblanks _         :=false, transpose:=false      source.select     rng2.offset(1, 0).select     range(selection, selection.end(xldown)).copy     target.select     range("c2").select     selection.pastespecial paste:=xlpastevalues, operation:=xlnone, skipblanks _         :=false, transpose:=false      source.select     rng3.offset(1, 0).select     range(selection, selection.end(xldown)).copy     target.select     range("d2").select     selection.pastespecial paste:=xlpastevalues, operation:=xlnone, skipblanks _         :=false, transpose:=false      source.select     rng4.offset(1, 0).select     range(selection, selection.end(xldown)).copy     target.select     range("e2").select     selection.pastespecial paste:=xlpastevalues, operation:=xlnone, skipblanks _         :=false, transpose:=false      source.select     rng5.offset(1, 0).select     range(selection, selection.end(xldown)).copy     target.select     range("f2").select     selection.pastespecial paste:=xlpastevalues, operation:=xlnone, skipblanks _         :=false, transpose:=false      source.select     rng6.offset(1, 0).select     range(selection, selection.end(xldown)).copy     target.select     range("g2").select     selection.pastespecial paste:=xlpastevalues, operation:=xlnone, skipblanks _         :=false, transpose:=false        lastrow = cells(rows.count, 5).end(xlup).row     range("a2:g" & lastrow).sort key1:=range("e2:e" & lastrow), order1:=xldescending, header:=xlno  source.select activesheet.autofiltermode = false  application.screenupdating = true application.displayalerts = true application.enableevents = true  msgbox "operation completed!" end sub

i've cleaned code; removing reliance on .select¹ , .activate¹ taking groups of variables , creating arrays each group. allowed loops shortened code while allowing full functionality.

sub af_update()     dim v long, vsearchcols variant, vcols variant, filterfor string     dim source worksheet, target worksheet      'application.screenupdating = false     'application.displayalerts = false     'application.enableevents = false      filterfor = "af*"      set source = thisworkbook.worksheets("raw data")     source         'array of 'searchcol' values on zero-based index         vsearchcols = array("prefix+short name", "site type", "sla target", "mean rtt (ms)", _                            "max rtt (ms)", "threshold 95%", "threshold 99%")         redim vcols(0 ubound(vsearchcols))  'make them same size         v = lbound(vsearchcols) ubound(vsearchcols)             vcols(v) = .rows(1).cells.find(what:=vsearchcols(v), lookin:=xlformulas, lookat:=xlwhole).column         next v     end      set target = worksheets("af")     target         if .autofiltermode .autofiltermode = false         .cells(1, 1).currentregion             debug.print .cells(.rows.count - 1, .columns.count).address(0, 0, external:=true)             .cells.resize(.rows.count - 1, .columns.count).offset(1, 0).clearcontents         end     end      source         if .autofiltermode .autofiltermode = false         .cells(1, 1).currentregion             .autofilter field:=vcols(0), criteria1:=filterfor              'check see if there copy across             .cells.resize(.rows.count - 1, .columns.count).offset(1, 0)                 if cbool(application.subtotal(103, .cells))                     'there transfer; loop through ranges                     v = lbound(vcols) ubound(vcols)                         .columns(vcols(v)).copy                         target.cells(2, v + 1).pastespecial paste:=xlpastevalues, operation:=xlnone, _                                                             skipblanks:=false, transpose:=false                     next v                 end if             end         end     end      target         .cells(1, 1).currentregion             .resize(.rows.count, 7)                 .cells.sort key1:=.columns(5), order1:=xldescending, _                             orientation:=xltoptobottom, header:=xlyes             end         end     end      application.screenupdating = true     application.displayalerts = true     application.enableevents = true      msgbox "operation completed!" end sub

you may wish step through code repeated f8 taps. i've temporarily commented out application environment changes.

when dealing block or 'island' of data originating a1, range.currentregion property fast , effective method of isolating data when referenced with ... end statement.

i had guess on worksheet macro code started. chose raw data worksheet.

¹ see how avoid using select in excel vba macros more methods on getting away relying on select , activate accomplish goals.


Comments

Post a Comment

Popular posts from this blog

get url and add instance to a model with prefilled foreign key :django admin -

Image
i have page in each contest shown in row. each row contains contest id , link other model names have foreign key contest. news model: class news(models.model): title = models.charfield(max_length=50, null=true) description = models.textfield() time = models.timefield( default= datetime.datetime.now().time()) contest = models.foreignkey(contest) i want use admin adding , changing these models. want when user clicks on 'news' has foreign key contest id=1, shows list of news value foreign key. use {% instance in instances %} <tr class="{% cycle 'row1' 'row2' %}"> <td>{{ instance.title }}</td> <td>{{ instance.starttime }}</td> <td>{{ instance.timespan }}</td> <td>{{ instance.date }}</td> <td><a href="/adm...
Read more

css - Make div keyboard-scrollable in jQuery Mobile? -

from can tell, although jquery-mobile-powered pages can contain divs overflow set scroll or auto, , these divs can scrolled one-screen bar or mouse wheel, cannot scrolled using arrow keys, page-up/page-down, or home/end. instead, official "page" div (with data-role="page") absorbs input. perhaps other divs can't acquire focus, i'm not sure. is there way around this? edit: jsfiddle of simple example: https://jsfiddle.net/qogz0shx/ <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.4/jquery.min.js"></script> <script src="https://ajax.googleapis.com/ajax/libs/jquerymobile/1.4.5/jquery.mobile.min.js"> </script> <link rel="stylesheet" href="https://code.jquery.com/mobile/1.4.5/jquery.mobile-1.4.5.css"/> <style> #outer { overflow:scroll; height: 50vh; width: 50vw; } #inner { height: 500vh; width: 500vw; } ...
Read more

ruby on rails - Seeing duplicate requests handled with Unicorn -

recently upgraded our ec2 instances c3.large in order boost more unicorn workers handle increased traffic our site. (6xworkers per machine - 2 ec2 instances). when did this, started seeing duplicate records being created! looks somehow, maybe 2 unicorn workers attempting process same request? in nginx logs see 1 [post] request particular rails controller - yet 2 records being created in database. i'm hitting kind of race condition issue - have no idea how debug this. doesn't happen time, when it's frustrating well. noticed 2 database records within 5 seconds of each other. (and yes disable button when user clicks submit button). unicorn 4.8.2 ruby 1.9.3 rails 3.2.14 any great! thanks! although may not fix problem, upgrade ruby 2.0.0. there blog mentioning idea upgrade ruby 2.0.0 if using unicorn. for example: https://www.digitalocean.com/community/articles/how-to-optimize-unicorn-workers-in-a-ruby-on-rails-app specifically: if using ruby 1.9, ...
Read more