| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Q re program speed, with 15k lines of data
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. -- John |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Q re program speed, with 15k lines of data
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 "asyado" wrote in message ... 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. -- John |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Q re program speed, with 15k lines of data
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 "RB Smissaert" wrote: 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 "asyado" wrote in message ... 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. -- John |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Q re program speed, with 15k lines of data
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 "asyado" wrote in message ... 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 "RB Smissaert" wrote: 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 "asyado" wrote in message ... 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. -- John |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Q re program speed, with 15k lines of data
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 "RB Smissaert" wrote: 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 "asyado" wrote in message ... 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 "RB Smissaert" wrote: 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 "asyado" wrote in message ... 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. -- John |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Q re program speed, with 15k lines of data
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 "asyado" wrote: 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 "RB Smissaert" wrote: 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 "asyado" wrote in message ... 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 "RB Smissaert" wrote: 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 "asyado" wrote in message ... 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. -- John |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| averaging lines of data at every ten lines | Excel Discussion (Misc queries) | |||
| Inserting Lines or Copying lines with formulas but without data | Excel Discussion (Misc queries) | |||
| Speed up Program Process | Excel Programming | |||
| Free Tool (Add-in) To Speed Up Your Data Processing | Excel Worksheet Functions | |||
| program speed problem | Excel Programming | |||
Hybrid Mode
