Q re program speed, with 15k lines of data

[Guest]

Hi,

Can anyone shed any light on the problem i'm having with following macro.
I have 15k lines of data on a worksheet. My program examines each line with
an if statement and depending on result inserts 3 new lines, adds 15 cells of
data for each line, or else moves to the next line.
I have screenupdating set to false, xlcalculation set to xlAutomatic.

This is the part i find curious - if i limit the number of lines of data to
<8,000 then the program runs in about 7-8 secs. If i try to run the program
with the full 15, lines it takes up to 18 mins to run.

Sorry if i've not provided all pertinant info, lemme know if i need to add
something else.
TIA.

 

[RB Smissaert]

Seeing the code will help.

Maybe what can speed this up is putting the data in an array, examine that
array, write to a second array and at the end write that second array back
to a sheet.

RBS

 

[Guest]

Hi RB.

I see your point about the arrays. You will see from my code below that i'm
inserting "vlookup" formulas, i should put that data into an array as an
initial procedure and then populate the cells from there!?

The thing is i guessed there were a couple of things to spped up my code,
but i couldn't understand why the code would run so quickly with say less
than 8000 lines, but then the performance dropped so substantially /
exponentially after that!

notes: int_Rows is calc'd earlier in the program to find the last row used.
the Do While loop operates on a cell that contains a recipe name. There are
many recipe_lines per recipe name. Each time a recipe name changes i want 3
lines to be inserted and be populated with data -some from another worksheet,
some static.

Thanking you.

n = 3
i = int_Rows + 2
Do While n < i
If Cells(n, 1) <> Cells(n - 1, 1) Then
Rows(n).Insert
Rows(n).Insert
Rows(n).Insert
Previous_Recipe_Code = VBA.CStr(Cells(n - 1, 1))
Current_Recipe_Code = VBA.CStr(Cells(n + 3, 1))
Cells(n, 1) = Previous_Recipe_Code
Cells(n + 1, 1) = Current_Recipe_Code
Cells(n + 2, 1) = Current_Recipe_Code
' Enter Material Output information
Cells(n, 2) = 1
Cells(n, 4) = 980
Cells(n, 5) = 5
Cells(n, 6) = "AR"
Cells(n, 7) = "AR"
Cells(n, 8) = "I"
Cells(n, 9) = VBA.CStr(VBA.Left(Previous_Recipe_Code, 6) & "MIX")
Cells(n, 10) = "B1"
Cells(n, 13) = 98
Cells(n, 14) = "KG"
Cells(n, 15) = "Y"
Cells(n, 16) = "N"
' enter labor information
Cells(n + 1, 2) = 1
Cells(n + 1, 4) = 960
Cells(n + 1, 5) = 2
Cells(n + 1, 6) = "BL"
Cells(n + 1, 7) = "BL"
Cells(n + 1, 12).Formula = "=vlookup(A" & n + 1 &
",'[compm4.xls]Workings'!$A$2:$D$1500,3,0)"
Cells(n + 1, 18).Formula = "=vlookup(A" & n + 1 &
",'[compm4.xls]Workings'!$A$2:$D$1500,4,0)"
Cells(n + 1, 19) = "R"
Cells(n + 1, 24) = "N"
' Enter Machine Information
Cells(n + 2, 2) = 1
Cells(n + 2, 4) = 970
Cells(n + 2, 5) = 3
Cells(n + 2, 6) = "BO"
Cells(n + 2, 7) = "BO"
Cells(n + 2, 11) = "Machine"
Cells(n + 2, 11).Formula = "=vlookup(A" & n + 2 &
",'[compm4.xls]Workings'!$A$2:$D$1500,2,0)"
Cells(n + 2, 18).Formula = "=vlookup(A" & n + 2 &
",'[compm4.xls]Workings'!$A$2:$D$1500,4,0)"
Cells(n + 2, 19) = "R"
Cells(n + 2, 22) = 0
Cells(n + 2, 23) = "M"
Cells(n + 2, 24) = "N"
n = n + 2
i = i + 3
End If
n = n + 1
Loop

 

[RB Smissaert]

I take it the slowness has to do with the lookup.
Would it help to put the lookup table in the same workbook?

I wouldn't think it slows things down much, but is this neccessary?

Previous_Recipe_Code = VBA.CStr(Cells(n - 1, 1))
Current_Recipe_Code = VBA.CStr(Cells(n + 3, 1))
Cells(n, 1) = Previous_Recipe_Code
Cells(n + 1, 1) = Current_Recipe_Code
Cells(n + 2, 1) = Current_Recipe_Code

Can't you just do:
Cells(n, 1) = Cells(n - 1, 1)
etc.

Maybe the main thing though is that you have set calculation to automatic.

I think you will have to do:
Application.Calculation = xlManual
before you start the loop and then do:
Application.Calculate
Application.Calculation = xlAutomatic
when the loop is finished.

If that doesn't work with your setup (giving the wrong data) then you my
have to revise your code.
I am not much into worksheet functions and tend to do evertything in VBA,
but maybe this will give you some ideas.
I still think doing the whole thing in arrays will be much faster.


RBS

Hi RB.

I see your point about the arrays. You will see from my code below that
i'm
inserting "vlookup" formulas, i should put that data into an array as an
initial procedure and then populate the cells from there!?

The thing is i guessed there were a couple of things to spped up my code,
but i couldn't understand why the code would run so quickly with say less
than 8000 lines, but then the performance dropped so substantially /
exponentially after that!

notes: int_Rows is calc'd earlier in the program to find the last row
used.
the Do While loop operates on a cell that contains a recipe name. There
are
many recipe_lines per recipe name. Each time a recipe name changes i want
3
lines to be inserted and be populated with data -some from another
worksheet,
some static.

Thanking you.

n = 3
i = int_Rows + 2
Do While n < i
If Cells(n, 1) <> Cells(n - 1, 1) Then
Rows(n).Insert
Rows(n).Insert
Rows(n).Insert
Previous_Recipe_Code = VBA.CStr(Cells(n - 1, 1))
Current_Recipe_Code = VBA.CStr(Cells(n + 3, 1))
Cells(n, 1) = Previous_Recipe_Code
Cells(n + 1, 1) = Current_Recipe_Code
Cells(n + 2, 1) = Current_Recipe_Code
' Enter Material Output information
Cells(n, 2) = 1
Cells(n, 4) = 980
Cells(n, 5) = 5
Cells(n, 6) = "AR"
Cells(n, 7) = "AR"
Cells(n, 8) = "I"
Cells(n, 9) = VBA.CStr(VBA.Left(Previous_Recipe_Code, 6) &
"MIX")
Cells(n, 10) = "B1"
Cells(n, 13) = 98
Cells(n, 14) = "KG"
Cells(n, 15) = "Y"
Cells(n, 16) = "N"
' enter labor information
Cells(n + 1, 2) = 1
Cells(n + 1, 4) = 960
Cells(n + 1, 5) = 2
Cells(n + 1, 6) = "BL"
Cells(n + 1, 7) = "BL"
Cells(n + 1, 12).Formula = "=vlookup(A" & n + 1 &
",'[compm4.xls]Workings'!$A$2:$D$1500,3,0)"
Cells(n + 1, 18).Formula = "=vlookup(A" & n + 1 &
",'[compm4.xls]Workings'!$A$2:$D$1500,4,0)"
Cells(n + 1, 19) = "R"
Cells(n + 1, 24) = "N"
' Enter Machine Information
Cells(n + 2, 2) = 1
Cells(n + 2, 4) = 970
Cells(n + 2, 5) = 3
Cells(n + 2, 6) = "BO"
Cells(n + 2, 7) = "BO"
Cells(n + 2, 11) = "Machine"
Cells(n + 2, 11).Formula = "=vlookup(A" & n + 2 &
",'[compm4.xls]Workings'!$A$2:$D$1500,2,0)"
Cells(n + 2, 18).Formula = "=vlookup(A" & n + 2 &
",'[compm4.xls]Workings'!$A$2:$D$1500,4,0)"
Cells(n + 2, 19) = "R"
Cells(n + 2, 22) = 0
Cells(n + 2, 23) = "M"
Cells(n + 2, 24) = "N"
n = n + 2
i = i + 3
End If
n = n + 1
Loop
--
John

Seeing the code will help.

Maybe what can speed this up is putting the data in an array, examine
that
array, write to a second array and at the end write that second array
back
to a sheet.

RBS

 

[Guest]

Hiya,

I think you're right, i didn't attribute much to the vlookup when the macro
ran quickly on my (small) test data set, but I should change it.
I'll post a reply with results to this thread in the next day or so, if you
want to check them out. Thanks for your suggestions.

BTW, oops line: I actually have the application.calculation set to xlManual
before the loop, not auotmatic as i stated in my firt post.


--
John

I take it the slowness has to do with the lookup.
Would it help to put the lookup table in the same workbook?

I wouldn't think it slows things down much, but is this neccessary?

Previous_Recipe_Code = VBA.CStr(Cells(n - 1, 1))
Current_Recipe_Code = VBA.CStr(Cells(n + 3, 1))
Cells(n, 1) = Previous_Recipe_Code
Cells(n + 1, 1) = Current_Recipe_Code
Cells(n + 2, 1) = Current_Recipe_Code

Can't you just do:
Cells(n, 1) = Cells(n - 1, 1)
etc.

Maybe the main thing though is that you have set calculation to automatic.

I think you will have to do:
Application.Calculation = xlManual
before you start the loop and then do:
Application.Calculate
Application.Calculation = xlAutomatic
when the loop is finished.

If that doesn't work with your setup (giving the wrong data) then you my
have to revise your code.
I am not much into worksheet functions and tend to do evertything in VBA,
but maybe this will give you some ideas.
I still think doing the whole thing in arrays will be much faster.


RBS

Hi RB.

I see your point about the arrays. You will see from my code below that
i'm
inserting "vlookup" formulas, i should put that data into an array as an
initial procedure and then populate the cells from there!?

The thing is i guessed there were a couple of things to spped up my code,
but i couldn't understand why the code would run so quickly with say less
than 8000 lines, but then the performance dropped so substantially /
exponentially after that!

notes: int_Rows is calc'd earlier in the program to find the last row
used.
the Do While loop operates on a cell that contains a recipe name. There
are
many recipe_lines per recipe name. Each time a recipe name changes i want
3
lines to be inserted and be populated with data -some from another
worksheet,
some static.

Thanking you.

n = 3
i = int_Rows + 2
Do While n < i
If Cells(n, 1) <> Cells(n - 1, 1) Then
Rows(n).Insert
Rows(n).Insert
Rows(n).Insert
Previous_Recipe_Code = VBA.CStr(Cells(n - 1, 1))
Current_Recipe_Code = VBA.CStr(Cells(n + 3, 1))
Cells(n, 1) = Previous_Recipe_Code
Cells(n + 1, 1) = Current_Recipe_Code
Cells(n + 2, 1) = Current_Recipe_Code
' Enter Material Output information
Cells(n, 2) = 1
Cells(n, 4) = 980
Cells(n, 5) = 5
Cells(n, 6) = "AR"
Cells(n, 7) = "AR"
Cells(n, 8) = "I"
Cells(n, 9) = VBA.CStr(VBA.Left(Previous_Recipe_Code, 6) &
"MIX")
Cells(n, 10) = "B1"
Cells(n, 13) = 98
Cells(n, 14) = "KG"
Cells(n, 15) = "Y"
Cells(n, 16) = "N"
' enter labor information
Cells(n + 1, 2) = 1
Cells(n + 1, 4) = 960
Cells(n + 1, 5) = 2
Cells(n + 1, 6) = "BL"
Cells(n + 1, 7) = "BL"
Cells(n + 1, 12).Formula = "=vlookup(A" & n + 1 &
",'[compm4.xls]Workings'!$A$2:$D$1500,3,0)"
Cells(n + 1, 18).Formula = "=vlookup(A" & n + 1 &
",'[compm4.xls]Workings'!$A$2:$D$1500,4,0)"
Cells(n + 1, 19) = "R"
Cells(n + 1, 24) = "N"
' Enter Machine Information
Cells(n + 2, 2) = 1
Cells(n + 2, 4) = 970
Cells(n + 2, 5) = 3
Cells(n + 2, 6) = "BO"
Cells(n + 2, 7) = "BO"
Cells(n + 2, 11) = "Machine"
Cells(n + 2, 11).Formula = "=vlookup(A" & n + 2 &
",'[compm4.xls]Workings'!$A$2:$D$1500,2,0)"
Cells(n + 2, 18).Formula = "=vlookup(A" & n + 2 &
",'[compm4.xls]Workings'!$A$2:$D$1500,4,0)"
Cells(n + 2, 19) = "R"
Cells(n + 2, 22) = 0
Cells(n + 2, 23) = "M"
Cells(n + 2, 24) = "N"
n = n + 2
i = i + 3
End If
n = n + 1
Loop
--
John

Seeing the code will help.

Maybe what can speed this up is putting the data in an array, examine
that
array, write to a second array and at the end write that second array
back
to a sheet.

RBS

Hi,

Can anyone shed any light on the problem i'm having with following
macro.
I have 15k lines of data on a worksheet. My program examines each line
with
an if statement and depending on result inserts 3 new lines, adds 15
cells
of
data for each line, or else moves to the next line.
I have screenupdating set to false, xlcalculation set to xlAutomatic.

This is the part i find curious - if i limit the number of lines of
data
to
<8,000 then the program runs in about 7-8 secs. If i try to run the
program
with the full 15, lines it takes up to 18 mins to run.

Sorry if i've not provided all pertinant info, lemme know if i need to
add
something else.
TIA.

 

[Guest]

all,

FYI: i replaced the vlookup formalae in the code below so the info is
populated from an array.
New code ran in approx 32 secs (compared to approx 18 mins).


--
John

Hiya,

I think you're right, i didn't attribute much to the vlookup when the macro
ran quickly on my (small) test data set, but I should change it.
I'll post a reply with results to this thread in the next day or so, if you
want to check them out. Thanks for your suggestions.

BTW, oops line: I actually have the application.calculation set to xlManual
before the loop, not auotmatic as i stated in my firt post.


--
John

I take it the slowness has to do with the lookup.
Would it help to put the lookup table in the same workbook?

I wouldn't think it slows things down much, but is this neccessary?

Previous_Recipe_Code = VBA.CStr(Cells(n - 1, 1))
Current_Recipe_Code = VBA.CStr(Cells(n + 3, 1))
Cells(n, 1) = Previous_Recipe_Code
Cells(n + 1, 1) = Current_Recipe_Code
Cells(n + 2, 1) = Current_Recipe_Code

Can't you just do:
Cells(n, 1) = Cells(n - 1, 1)
etc.

Maybe the main thing though is that you have set calculation to automatic.

I think you will have to do:
Application.Calculation = xlManual
before you start the loop and then do:
Application.Calculate
Application.Calculation = xlAutomatic
when the loop is finished.

If that doesn't work with your setup (giving the wrong data) then you my
have to revise your code.
I am not much into worksheet functions and tend to do evertything in VBA,
but maybe this will give you some ideas.
I still think doing the whole thing in arrays will be much faster.


RBS

Hi RB.

I see your point about the arrays. You will see from my code below that
i'm
inserting "vlookup" formulas, i should put that data into an array as an
initial procedure and then populate the cells from there!?

The thing is i guessed there were a couple of things to spped up my code,
but i couldn't understand why the code would run so quickly with say less
than 8000 lines, but then the performance dropped so substantially /
exponentially after that!

notes: int_Rows is calc'd earlier in the program to find the last row
used.
the Do While loop operates on a cell that contains a recipe name. There
are
many recipe_lines per recipe name. Each time a recipe name changes i want
3
lines to be inserted and be populated with data -some from another
worksheet,
some static.

Thanking you.

n = 3
i = int_Rows + 2
Do While n < i
If Cells(n, 1) <> Cells(n - 1, 1) Then
Rows(n).Insert
Rows(n).Insert
Rows(n).Insert
Previous_Recipe_Code = VBA.CStr(Cells(n - 1, 1))
Current_Recipe_Code = VBA.CStr(Cells(n + 3, 1))
Cells(n, 1) = Previous_Recipe_Code
Cells(n + 1, 1) = Current_Recipe_Code
Cells(n + 2, 1) = Current_Recipe_Code
' Enter Material Output information
Cells(n, 2) = 1
Cells(n, 4) = 980
Cells(n, 5) = 5
Cells(n, 6) = "AR"
Cells(n, 7) = "AR"
Cells(n, 8) = "I"
Cells(n, 9) = VBA.CStr(VBA.Left(Previous_Recipe_Code, 6) &
"MIX")
Cells(n, 10) = "B1"
Cells(n, 13) = 98
Cells(n, 14) = "KG"
Cells(n, 15) = "Y"
Cells(n, 16) = "N"
' enter labor information
Cells(n + 1, 2) = 1
Cells(n + 1, 4) = 960
Cells(n + 1, 5) = 2
Cells(n + 1, 6) = "BL"
Cells(n + 1, 7) = "BL"
Cells(n + 1, 12).Formula = "=vlookup(A" & n + 1 &
",'[compm4.xls]Workings'!$A$2:$D$1500,3,0)"
Cells(n + 1, 18).Formula = "=vlookup(A" & n + 1 &
",'[compm4.xls]Workings'!$A$2:$D$1500,4,0)"
Cells(n + 1, 19) = "R"
Cells(n + 1, 24) = "N"
' Enter Machine Information
Cells(n + 2, 2) = 1
Cells(n + 2, 4) = 970
Cells(n + 2, 5) = 3
Cells(n + 2, 6) = "BO"
Cells(n + 2, 7) = "BO"
Cells(n + 2, 11) = "Machine"
Cells(n + 2, 11).Formula = "=vlookup(A" & n + 2 &
",'[compm4.xls]Workings'!$A$2:$D$1500,2,0)"
Cells(n + 2, 18).Formula = "=vlookup(A" & n + 2 &
",'[compm4.xls]Workings'!$A$2:$D$1500,4,0)"
Cells(n + 2, 19) = "R"
Cells(n + 2, 22) = 0
Cells(n + 2, 23) = "M"
Cells(n + 2, 24) = "N"
n = n + 2
i = i + 3
End If
n = n + 1
Loop
--
John


:

Seeing the code will help.

Maybe what can speed this up is putting the data in an array, examine
that
array, write to a second array and at the end write that second array
back
to a sheet.

RBS

Hi,

Can anyone shed any light on the problem i'm having with following
macro.
I have 15k lines of data on a worksheet. My program examines each line
with
an if statement and depending on result inserts 3 new lines, adds 15
cells
of
data for each line, or else moves to the next line.
I have screenupdating set to false, xlcalculation set to xlAutomatic.

This is the part i find curious - if i limit the number of lines of
data
to
<8,000 then the program runs in about 7-8 secs. If i try to run the
program
with the full 15, lines it takes up to 18 mins to run.

Sorry if i've not provided all pertinant info, lemme know if i need to
add
something else.
TIA.