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
'++++++++++++++++++++++++++++++++++++++

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
'++++++++++++++++++++++++++++++++++++++

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
'++++++++++++++++++++++++++++++++++++++

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 -

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