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