Boy oh Boy...Is this baby Slow!

Posted by Simon McArdle on May 16, 2001 2:37 AM

Hi,

I have this Macro ...see below....that takes around 5 minutes to complete. Is there anyway that I can speed this up. Is there anyway I can condense this into something alot smaller.

Sub Logger()

Dim FileNm As String 
Dim Temp As String 
Dim x As Integer

FileNm = Application.GetOpenFilename

If FileNm = "False" Then End

Workbooks.Open FileNm

application.screenupdating = false

Windows("LITmaster_V0_1.xls").Activate


Sheets("L-EQPT").Select
ActiveCell.FormulaR1C1 = ""
Range("U8").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISBLANK(RC[-1]),"""",IF(ISNUMBER(MATCH(RC[-9],doggy.csv!R1C1:R2000C1,0)),VLOOKUP(RC[-9],doggy.csv!R1C1:R2000C2,2,0),""""))"
Selection.AutoFill Destination:=Range("U8:U265"), Type:=xlFillDefault
Range("U8:U265").Select
Sheets("L-ACU").Select
Range("U8").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISBLANK(RC[-1]),"""",IF(ISNUMBER(MATCH(RC[-9],doggy.csv!R1C1:R2000C1,0)),VLOOKUP(RC[-9],doggy.csv!R1C1:R2000C2,2,0),""""))"
Selection.AutoFill Destination:=Range("U8:U21"), Type:=xlFillDefault
Range("U8:U21").Select
Sheets("L-ATM").Select
Range("U8").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISBLANK(RC[-1]),"""",IF(ISNUMBER(MATCH(RC[-9],doggy.csv!R1C1:R2000C1,0)),VLOOKUP(RC[-9],doggy.csv!R1C1:R2000C2,2,0),""""))"
Selection.AutoFill Destination:=Range("U8:U420"), Type:=xlFillDefault
Range("U8:U420").Select
Sheets("L-ADSL").Select
Range("U8").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISBLANK(RC[-1]),"""",IF(ISNUMBER(MATCH(RC[-9],doggy.csv!R1C1:R2000C1,0)),VLOOKUP(RC[-9],doggy.csv!R1C1:R2000C2,2,0),""""))"
Selection.AutoFill Destination:=Range("U8:U167"), Type:=xlFillDefault
Range("U8:U167").Select
Sheets("L-EXT").Select
Range("U8").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISBLANK(RC[-1]),"""",IF(ISNUMBER(MATCH(RC[-9],doggy.csv!R1C1:R2000C1,0)),VLOOKUP(RC[-9],doggy.csv!R1C1:R2000C2,2,0),""""))"
Selection.AutoFill Destination:=Range("U8:U56"), Type:=xlFillDefault
Range("U8:U56").Select
Sheets("L-POW").Select
Range("U8").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISBLANK(RC[-1]),"""",IF(ISNUMBER(MATCH(RC[-9],doggy.csv!R1C1:R2000C1,0)),VLOOKUP(RC[-9],doggy.csv!R1C1:R2000C2,2,0),""""))"
Selection.AutoFill Destination:=Range("U8:U32"), Type:=xlFillDefault
Range("U8:U32").Select
Sheets("L-ALRM").Select
Range("U8").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISBLANK(RC[-1]),"""",IF(ISNUMBER(MATCH(RC[-9],doggy.csv!R1C1:R2000C1,0)),VLOOKUP(RC[-9],doggy.csv!R1C1:R2000C2,2,0),""""))"
Selection.AutoFill Destination:=Range("U8:U20"), Type:=xlFillDefault
Range("U8:U20").Select
Sheets("L-TRAN").Select
Range("U8").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISBLANK(RC[-1]),"""",IF(ISNUMBER(MATCH(RC[-9],doggy.csv!R1C1:R2000C1,0)),VLOOKUP(RC[-9],doggy.csv!R1C1:R2000C2,2,0),""""))"
Selection.AutoFill Destination:=Range("U8:U11"), Type:=xlFillDefault
Range("U8:U11").Select
Sheets("L-TRAP").Select
Range("U8").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISBLANK(RC[-1]),"""",IF(ISNUMBER(MATCH(RC[-9],doggy.csv!R1C1:R2000C1,0)),VLOOKUP(RC[-9],doggy.csv!R1C1:R2000C2,2,0),""""))"
Selection.AutoFill Destination:=Range("U8:U30"), Type:=xlFillDefault
Range("U8:U30").Select
Sheets("L-SNTP").Select
Range("U8").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISBLANK(RC[-1]),"""",IF(ISNUMBER(MATCH(RC[-9],doggy.csv!R1C1:R2000C1,0)),VLOOKUP(RC[-9],doggy.csv!R1C1:R2000C2,2,0),""""))"
Selection.AutoFill Destination:=Range("U8:U32"), Type:=xlFillDefault
Range("U8:U32").Select
Sheets("L-TFTP").Select
Range("U8").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISBLANK(RC[-1]),"""",IF(ISNUMBER(MATCH(RC[-9],doggy.csv!R1C1:R2000C1,0)),VLOOKUP(RC[-9],doggy.csv!R1C1:R2000C2,2,0),""""))"
Selection.AutoFill Destination:=Range("U8:U12"), Type:=xlFillDefault
Range("U8:U12").Select
Sheets("L-IP").Select
Range("U8").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISBLANK(RC[-1]),"""",IF(ISNUMBER(MATCH(RC[-9],doggy.csv!R1C1:R2000C1,0)),VLOOKUP(RC[-9],doggy.csv!R1C1:R2000C2,2,0),""""))"
Selection.AutoFill Destination:=Range("U8:U9"), Type:=xlFillDefault
Range("U8:U9").Select
Sheets("L-UPGR").Select
Range("U8").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISBLANK(RC[-1]),"""",IF(ISNUMBER(MATCH(RC[-9],doggy.csv!R1C1:R2000C1,0)),VLOOKUP(RC[-9],doggy.csv!R1C1:R2000C2,2,0),""""))"
Selection.AutoFill Destination:=Range("U8:U91"), Type:=xlFillDefault
Range("U8:U91").Select
Sheets("L-ITF").Select
Range("U8").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISBLANK(RC[-1]),"""",IF(ISNUMBER(MATCH(RC[-9],doggy.csv!R1C1:R2000C1,0)),VLOOKUP(RC[-9],doggy.csv!R1C1:R2000C2,2,0),""""))"
Selection.AutoFill Destination:=Range("U8:U54"), Type:=xlFillDefault
Range("U8:U54").Select
Sheets("L-RDCY").Select
Range("U8").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISBLANK(RC[-1]),"""",IF(ISNUMBER(MATCH(RC[-9],doggy.csv!R1C1:R2000C1,0)),VLOOKUP(RC[-9],doggy.csv!R1C1:R2000C2,2,0),""""))"
Selection.AutoFill Destination:=Range("U8:U180"), Type:=xlFillDefault
Range("U8:U180").Select
Sheets("L-WRAP").Select
Range("U8").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISBLANK(RC[-1]),"""",IF(ISNUMBER(MATCH(RC[-9],doggy.csv!R1C1:R2000C1,0)),VLOOKUP(RC[-9],doggy.csv!R1C1:R2000C2,2,0),""""))"
Selection.AutoFill Destination:=Range("U8:U25"), Type:=xlFillDefault
Range("U8:U25").Select
Sheets("L-LOAD").Select
Range("U8").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISBLANK(RC[-1]),"""",IF(ISNUMBER(MATCH(RC[-9],doggy.csv!R1C1:R2000C1,0)),VLOOKUP(RC[-9],doggy.csv!R1C1:R2000C2,2,0),""""))"
Selection.AutoFill Destination:=Range("U8:U33"), Type:=xlFillDefault
Range("U8:U33").Select
Sheets("L-FASM").Select
Range("U8").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISBLANK(RC[-1]),"""",IF(ISNUMBER(MATCH(RC[-9],doggy.csv!R1C1:R2000C1,0)),VLOOKUP(RC[-9],doggy.csv!R1C1:R2000C2,2,0),""""))"
Selection.AutoFill Destination:=Range("U8:U65"), Type:=xlFillDefault
Range("U8:U65").Select
Sheets("L-DFCE").Select
Range("U8").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISBLANK(RC[-1]),"""",IF(ISNUMBER(MATCH(RC[-9],doggy.csv!R1C1:R2000C1,0)),VLOOKUP(RC[-9],doggy.csv!R1C1:R2000C2,2,0),""""))"
Selection.AutoFill Destination:=Range("U8:U14"), Type:=xlFillDefault
Range("U8:U14").Select
Sheets("L-GOLD").Select
Range("U8").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISBLANK(RC[-1]),"""",IF(ISNUMBER(MATCH(RC[-9],doggy.csv!R1C1:R2000C1,0)),VLOOKUP(RC[-9],doggy.csv!R1C1:R2000C2,2,0),""""))"
Selection.AutoFill Destination:=Range("U8:U101"), Type:=xlFillDefault
Range("U8:U101").Select
Sheets("L-F4F5").Select
Range("U8").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISBLANK(RC[-1]),"""",IF(ISNUMBER(MATCH(RC[-9],doggy.csv!R1C1:R2000C1,0)),VLOOKUP(RC[-9],doggy.csv!R1C1:R2000C2,2,0),""""))"
Selection.AutoFill Destination:=Range("U8:U26"), Type:=xlFillDefault
Range("U8:U26").Select
Sheets("L-BURE").Select
Range("U8").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISBLANK(RC[-1]),"""",IF(ISNUMBER(MATCH(RC[-9],doggy.csv!R1C1:R2000C1,0)),VLOOKUP(RC[-9],doggy.csv!R1C1:R2000C2,2,0),""""))"
Selection.AutoFill Destination:=Range("U8:U107"), Type:=xlFillDefault
Range("U8:U107").Select
Sheets("L-FAUP").Select
Range("U8").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISBLANK(RC[-1]),"""",IF(ISNUMBER(MATCH(RC[-9],doggy.csv!R1C1:R2000C1,0)),VLOOKUP(RC[-9],doggy.csv!R1C1:R2000C2,2,0),""""))"
Selection.AutoFill Destination:=Range("U8:U26"), Type:=xlFillDefault
Range("U8:U26").Select
Sheets("L-TEBU").Select
Range("U8").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISBLANK(RC[-1]),"""",IF(ISNUMBER(MATCH(RC[-9],doggy.csv!R1C1:R2000C1,0)),VLOOKUP(RC[-9],doggy.csv!R1C1:R2000C2,2,0),""""))"
Selection.AutoFill Destination:=Range("U8:U54"), Type:=xlFillDefault
Range("U8:U54").Select
Sheets("L-COMB").Select
Range("U8").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISBLANK(RC[-1]),"""",IF(ISNUMBER(MATCH(RC[-9],doggy.csv!R1C1:R2000C1,0)),VLOOKUP(RC[-9],doggy.csv!R1C1:R2000C2,2,0),""""))"
Selection.AutoFill Destination:=Range("U8:U31"), Type:=xlFillDefault
Range("U8:U31").Select
Sheets("L-MIGR").Select
Range("U8").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISBLANK(RC[-1]),"""",IF(ISNUMBER(MATCH(RC[-9],doggy.csv!R1C1:R2000C1,0)),VLOOKUP(RC[-9],doggy.csv!R1C1:R2000C2,2,0),""""))"
Selection.AutoFill Destination:=Range("U8:U29"), Type:=xlFillDefault
Range("U8:U29").Select
Sheets("L-OM").Select
Range("U8").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISBLANK(RC[-1]),"""",IF(ISNUMBER(MATCH(RC[-9],doggy.csv!R1C1:R2000C1,0)),VLOOKUP(RC[-9],doggy.csv!R1C1:R2000C2,2,0),""""))"
Selection.AutoFill Destination:=Range("U8:U118"), Type:=xlFillDefault
Range("U8:U118").Select
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("Statistics").Select
End Sub

Thank you for your help.

Simon

Posted by Dave Hawley on May 16, 2001 5:15 AM

Hi Simon

What a monster !

Selecting cells can really slow down macros, and theres no real need for AutoFill eg;

Sheets("L-EQPT").Range("U8:U256").FormulaR1C1 = _
"=IF(ISBLANK(RC[-1]),"""",IF(ISNUMBER(MATCH" _
& "(RC[-9],doggy.csv!R1C1:R2000C1,0)),VLOOKUP(RC[-9],doggy.csv!R1C1:R2000C2,2,0),""""))"

Will do the job. Change all your code to look like this and it should run a LOT smoother.

Dave
OzGrid Business Applications