Excel VBA - very slow, and weird behaviour - Something else running? -
ok - excel vba -
is running slow, , weird behaviour - else running?
- the text i'm typing gets "rearranged" automatically (ie. first letter type @ end of sudden)
- the text of current line i'm on turns red
- in middle of typing something, window pop "syntax error" before i'm done typing.
i have turned off addins (i have tm1 - pain in rear end)
i have optimised code using, , have been coding long time , there nothing should taking long...
help!!!
sub createcopy3() dim x long dim sumfilterno long dim m long dim delme long dim ncount long dim lrowc_dow dim newsh string dim mp long dim shdow dim shdata string dim shcons string dim shxx string dim shdowxx string dim sfilter string dim sfiltercol string dim sfiltercolnumber long dim shname string dim sfilterby string dim lrowc long dim lrowc_sum long dim lrowc_new long dim nicename string dim l long dim rptfilteredby string dim llastrow long, llastcolumn long dim lreallastrow long, lreallastcolumn long dim arragent() string dim j long application.screenupdating = false shdowxx = "dow xx" shxx = "zz" shdata = "data" shcons = "consolidated" sheets("summary").select sfilter = range("b2").value sfilterby = range("b3").value lrowc = activesheet.usedrange.rows.count - 11 select case sfilter case "agent_code" shname = "agent" sfiltercol = "j" sumfilterno = 1 nicename = "agent code" sfiltercolnumber = 1 case "account_manager" sfiltercol = "f" shname = "am" sumfilterno = 5 nicename = "account manager" sfiltercolnumber = 30 case "regional_sales_manager" sfiltercol = "g" sumfilterno = 6 shname = "sm" sfiltercolnumber = 31 nicename = "reg. sales manager" case "customer" shname = "customer" sfiltercol = "i" sumfilterno = 9 nicename = "customer" sfiltercolnumber = 33 case "region" shname = "region" sfiltercol = "c" sumfilterno = 2 nicename = "region" sfiltercolnumber = 29 case "top_level_region" sumfilterno = 1 shname = "top region" sfiltercol = "b" nicename = "top level region" sfiltercolnumber = 28 case else msgbox "no selection - operation cancelled" exit sub end select rptfilteredby = nicename & " filtered " & range("b3").value range("b9").value = rptfilteredby application.displayalerts = false worksheets(shdata).activate lrowc = activesheet.usedrange.rows.count sheets("summary").select 'range("a13:z" & lrowc).clear if activesheet.autofiltermode = true ' range("a3:az3").select selection.autofilter end if range("a13:z" & lrowc).clear worksheets(shcons).activate if activesheet.autofiltermode = false range("a3:az3").select selection.autofilter end if if activesheet.autofiltermode = true range("a3:az3").select selection.autofilter end if if activesheet.autofiltermode = false range("a3:az3").select selection.autofilter end if activesheet.range("$a$3:$az$" & lrowc).autofilter field:=sfiltercolnumber, criteria1:= _ sfilterby, operator:=xland range("g11").select range("a3").select range(selection, selection.end(xldown)).select selection.copy sheets("summary").select range("a12").activate range("b1").formular1c1 = "=counta(r[12]c[-1]:r[" & lrowc & "]c[-1])" selection.pastespecial paste:=xlpastevalues, operation:=xlnone, skipblanks:=false, transpose:=false application.cutcopymode = false activesheet.range("$a$13:$a$" & lrowc + 10 & "").removeduplicates columns:=1, header:=xlno range("b13").select '**************** remove errors ********************** if activesheet.autofiltermode = true range("a12:az12").select selection.autofilter end if application.statusbar = "calculations summary page" lrowc_sum = range("b1").value + 12 if lrowc_sum < 13 lrowc_sum = 13 range("b13").activate range("b13:c" & lrowc & ",e13:m1" & lrowc & "").formular1c1 = _ "=index(consolidated!r3c1:r" & lrowc & "c73,match(rc1,consolidated!c1,0),match(r5c,consolidated!r3c1:r3c53,0))" ' range("b13:z" & lrowc).value = range("b13:z" & lrowc).value range("d13:d" & lrowc).formular1c1 = "=""vs""&left(rc[-3],4)" range("d13:d" & lrowc).value = range("d13:d" & lrowc).value range("o13:o" & lrowc).formular1c1 = "=countif(consolidated!c1,rc1)" range("q13:q" & lrowc).formular1c1 = "=sumif(consolidated!c1,rc1,consolidated!c[-4])" range("r13:r" & lrowc).formular1c1 = "=sumif(consolidated!c1,rc1,consolidated!c[-4])" range("p13:p" & lrowc).formular1c1 = "=sum(rc[1]:rc[2])" range("s13:s" & lrowc).formular1c1 = "=rc[-1]/rc[-4]" range("t13:t" & lrowc).formular1c1 = "=sumif(consolidated!c1,rc1,consolidated!c[-4])" range("u13:u" & lrowc).formular1c1 = "=sumif(consolidated!c1,rc1,consolidated!c[-3])" range("v13:v" & lrowc).formular1c1 = "=rc[-1]/rc[-2]" range("w13:w" & lrowc).formular1c1 = "=sumif(consolidated!c1,rc1,consolidated!c[-6])" range("x13:x" & lrowc).formular1c1 = "=sumif(consolidated!c1,rc1,consolidated!c[-5])" range("y13:y" & lrowc).formular1c1 = "=rc[-1]/rc[-2]" range("o10").formular1c1 = "=sum(r[3]c:r[" & lrowc_sum & "]c)" range("p10").formular1c1 = "=sum(r[3]c:r[" & lrowc_sum & "]c)" range("q10").formular1c1 = "=sum(r[3]c:r[" & lrowc_sum & "]c)" range("r10").formular1c1 = "=sum(r[3]c:r[" & lrowc_sum & "]c)" range("s10").formular1c1 = "=sum(rc[-2]/rc[-4])" range("t10").formular1c1 = "=sum(r[3]c:r[" & lrowc_sum & "]c)" range("u10").formular1c1 = "=sum(r[3]c:r[" & lrowc_sum & "]c)" range("v10").formular1c1 = "=sum(rc[-1]/rc[-2])" range("w10").formular1c1 = "=sum(r[3]c:r[" & lrowc_sum & "]c)" range("x10").formular1c1 = "=sum(r[3]c:r[" & lrowc_sum & "]c)" range("y10").formular1c1 = "=sum(rc[-1]/rc[-2])" range("x13").select range("b13:da" & lrowc_sum).numberformat = "#,###;[red](#,###)" range("s13:s" & lrowc_sum).style = "percent" range("v13:v" & lrowc_sum).style = "percent" range("y13:y" & lrowc_sum).style = "percent" range("n13:n" & lrowc_sum).numberformat = "0" range("k13:k" & lrowc_sum).numberformat = "0" application.calculation = xlcalculationautomatic range("b1").formular1c1 = "=counta(r[12]c[1]:r[" & lrowc & "]c[1])" lrowc = range("b1").value range("a12:az12").select '**************** remove errors ********************** if activesheet.autofiltermode = false range("a12:az12").select selection.autofilter end if on error resume next activesheet.range("$a$12:$az" & lrowc_sum).autofilter field:=2, criteria1:="#n/a" on error goto 0 application.calculation = xlcalculationmanual range("a12").select activecell.offset(1, 0).select if activecell.value = "" exit loop until activecell.entirerow.hidden = false range(selection, selection.end(xldown)).select selection.delete shift:=xlup if activesheet.autofiltermode = true selection.autofilter end if if activesheet.autofiltermode = false selection.autofilter end if on error resume next activesheet.range("$a$12:$az$" & lrowc_sum).autofilter field:=13, criteria1:="0" on error goto 0 activecell.offset(1, 0).select if activecell.value = "" exit loop until activecell.entirerow.hidden = false range("g2").select '**************** errors removed ********************** application.statusbar = "formatting...." range("b1").formular1c1 = "=counta(r[12]c[1]:r[" & lrowc & "]c[1])" lrowc = range("b1").value application.statusbar = "" msgbox "summary reports created " & vbcrlf & nicename & " " & sfilterby application.screenupdating = false end sub
have checked excel workbook code? mean there no update code in workbook not vba modules.
have tried turning off excel recalc , display while code running?
application.calculation = xlmanual ' later setting automatic, , application.screenupdating = false ' code , stop screen flickering application.screenupdating = trueis rogue excel version open? if restart current instance - errors when excel 32bit has been opened app when working in excel 64bit. @ task manager.
is sheet corrupted? last resort have tried opening new workbook , copying data , code it, , resaving fresh. have used past erratic behaviour big sheets on network.
just trying track down irritating behaviour.
Comments
Post a Comment