| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Slow code needs speeding up!....any ideas??....
I've been running the below code that loops through about 50,000 rows
of one sheet. It checks one of the columns to see if a certain criteria is matched (ie.that the value in the cell can be found in an array that is being held in the variable mySetUpArray) and if so it then transfers various data to other places in the workbook (using named ranges). The code takes ages to execute - can anyone spot any parts of the code that could be made more efficient and thus speed things up?? Any help greatly appreciated, Jason. '++++++++++++++++++++++++++++++++++++++ Worksheets("Data").Select With Sheets("Data") For Each cell In myRange 'next line will check to see if the value of the cell matches any of the 'elements in the array mySetUpArray If Not IsError(Application.Match(cell, mySetUpArray, 0)) Then 'find the name used in the relevent graph named range myGraph = WorksheetFunction.VLookup(cell, Range("Tournaments"), 6, False) 'check to see if it is a defunct tournament or not If myGraph < "n/a" Then With Range("ID_" & myGraph) .End(xlDown)(2, 1) = cell(1, 0) 'date .End(xlDown)(1, 2) = cell(1, 7) 'num of A .End(xlDown)(1, 3) = cell(1, 16) 'prize .End(xlDown)(1, 4) = cell(1, 23) 'special amount .End(xlDown)(1, 5) = cell(1, 8) 'time .End(xlDown)(1, 6) = cell(1, -2) 'date .End(xlDown)(1, 7) = cell(1, 1) 'SetUp ID End With End If End If Next cell End With '++++++++++++++++++++++++++++++++++++++ |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Slow code needs speeding up!....any ideas??....
application.screenupdating=false
application.calculation=xlmanual do stuff application.screenupdating=true application.calculation=xlautomatic -- Tim Williams Palo Alto, CA "WhytheQ" wrote in message oups.com... I've been running the below code that loops through about 50,000 rows of one sheet. It checks one of the columns to see if a certain criteria is matched (ie.that the value in the cell can be found in an array that is being held in the variable mySetUpArray) and if so it then transfers various data to other places in the workbook (using named ranges). The code takes ages to execute - can anyone spot any parts of the code that could be made more efficient and thus speed things up?? Any help greatly appreciated, Jason. '++++++++++++++++++++++++++++++++++++++ Worksheets("Data").Select With Sheets("Data") For Each cell In myRange 'next line will check to see if the value of the cell matches any of the 'elements in the array mySetUpArray If Not IsError(Application.Match(cell, mySetUpArray, 0)) Then 'find the name used in the relevent graph named range myGraph = WorksheetFunction.VLookup(cell, Range("Tournaments"), 6, False) 'check to see if it is a defunct tournament or not If myGraph < "n/a" Then With Range("ID_" & myGraph) .End(xlDown)(2, 1) = cell(1, 0) 'date .End(xlDown)(1, 2) = cell(1, 7) 'num of A .End(xlDown)(1, 3) = cell(1, 16) 'prize .End(xlDown)(1, 4) = cell(1, 23) 'special amount .End(xlDown)(1, 5) = cell(1, 8) 'time .End(xlDown)(1, 6) = cell(1, -2) 'date .End(xlDown)(1, 7) = cell(1, 1) 'SetUp ID End With End If End If Next cell End With '++++++++++++++++++++++++++++++++++++++ |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Slow code needs speeding up!....any ideas??....
should be xlCalculationManual xlCalculationAutomatic -- Tim Williams Palo Alto, CA "Tim Williams" <timjwilliams at gmail dot com wrote in message ... application.screenupdating=false application.calculation=xlmanual do stuff application.screenupdating=true application.calculation=xlautomatic -- Tim Williams Palo Alto, CA "WhytheQ" wrote in message oups.com... I've been running the below code that loops through about 50,000 rows of one sheet. It checks one of the columns to see if a certain criteria is matched (ie.that the value in the cell can be found in an array that is being held in the variable mySetUpArray) and if so it then transfers various data to other places in the workbook (using named ranges). The code takes ages to execute - can anyone spot any parts of the code that could be made more efficient and thus speed things up?? Any help greatly appreciated, Jason. '++++++++++++++++++++++++++++++++++++++ Worksheets("Data").Select With Sheets("Data") For Each cell In myRange 'next line will check to see if the value of the cell matches any of the 'elements in the array mySetUpArray If Not IsError(Application.Match(cell, mySetUpArray, 0)) Then 'find the name used in the relevent graph named range myGraph = WorksheetFunction.VLookup(cell, Range("Tournaments"), 6, False) 'check to see if it is a defunct tournament or not If myGraph < "n/a" Then With Range("ID_" & myGraph) .End(xlDown)(2, 1) = cell(1, 0) 'date .End(xlDown)(1, 2) = cell(1, 7) 'num of A .End(xlDown)(1, 3) = cell(1, 16) 'prize .End(xlDown)(1, 4) = cell(1, 23) 'special amount .End(xlDown)(1, 5) = cell(1, 8) 'time .End(xlDown)(1, 6) = cell(1, -2) 'date .End(xlDown)(1, 7) = cell(1, 1) 'SetUp ID End With End If End If Next cell End With '++++++++++++++++++++++++++++++++++++++ |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Slow code needs speeding up!....any ideas??....
Thanks Tim: I'm already turning off the screenupdating, but I'll try
turning calculation to manual - that oftem seems to work. Rgds J On Mar 6, 6:17 pm, "Tim Williams" <timjwilliams at gmail dot com wrote: should be xlCalculationManual xlCalculationAutomatic -- Tim Williams Palo Alto, CA "Tim Williams" <timjwilliams at gmail dot com wrote in l... application.screenupdating=false application.calculation=xlmanual do stuff application.screenupdating=true application.calculation=xlautomatic -- Tim Williams Palo Alto, CA "WhytheQ" wrote in ooglegroups.com... I've been running the below code that loops through about 50,000 rows of one sheet. It checks one of the columns to see if a certain criteria is matched (ie.that the value in the cell can be found in an array that is being held in the variable mySetUpArray) and if so it then transfers various data to other places in the workbook (using named ranges). The code takes ages to execute - can anyone spot any parts of the code that could be made more efficient and thus speed things up?? Any help greatly appreciated, Jason. '++++++++++++++++++++++++++++++++++++++ Worksheets("Data").Select With Sheets("Data") For Each cell In myRange 'next line will check to see if the value of the cell matches any of the 'elements in the array mySetUpArray If Not IsError(Application.Match(cell, mySetUpArray, 0)) Then 'find the name used in the relevent graph named range myGraph = WorksheetFunction.VLookup(cell, Range("Tournaments"), 6, False) 'check to see if it is a defunct tournament or not If myGraph < "n/a" Then With Range("ID_" & myGraph) .End(xlDown)(2, 1) = cell(1, 0) 'date .End(xlDown)(1, 2) = cell(1, 7) 'num of A .End(xlDown)(1, 3) = cell(1, 16) 'prize .End(xlDown)(1, 4) = cell(1, 23) 'special amount .End(xlDown)(1, 5) = cell(1, 8) 'time .End(xlDown)(1, 6) = cell(1, -2) 'date .End(xlDown)(1, 7) = cell(1, 1) 'SetUp ID End With End If End If Next cell End With '++++++++++++++++++++++++++++++++++++++- Hide quoted text - - Show quoted text - |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Slow code needs speeding up!....any ideas??....
When the performance matters you can easily forget such approach after 2.000 rows let alone your 50.000 ones. For Each cell In myRange Look for other options to reconstract your solution such as: ..Find Next ..Advanced Filter ..Pivot Table etc If you still want to stay within Excel of course. Good Luck |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Help speeding up my code | Excel Programming | |||
| speeding up copy and paste code | Excel Programming | |||
| Wow. This is interesting and slow. Any ideas? | Excel Programming | |||
| Help on cleaning / speeding up code | Excel Programming | |||
| Speeding Up Code | Excel Programming | |||
Linear Mode
