Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Speeding up execution of a Macro
I'm running a macro that pull data from excel file #1 to Excel File #2
The Macro is in excel file #2 it uses the sumif and offset formulas in combination with a ranged name linked to excel file #1 (there are 43 worksheets of informatiuon in this file, I need from 19 of these worksheets) Formula: [DataArray = range referrenced in Excel file #1, changes based on column in excel file #2] ActiveCell.FormulaR1C1 = _ "=SUMIF(" & DataArray & ",RC1,OFFSET(" & DataArray & ",0,R1C,ROWS(" & DataArray & "),1))" Excel file #2 has 228 column of data and 1000 rows I use a nested For Next loop to go through the full range and add the correct formula to each cell. the copy the full range of data then paste value it before turning back on calculations. This works fine but I have to do this for five sheet in the woorbook, and after running the macro excel becomes slow and slower does any on have a better way of doinmg this that would be faster? Thanks -- Helping Is always a good thing |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Speeding up execution of a Macro
we'd have to have all the code for anything too detailed, but when you are
working with formulas and such on a worksheet always start your code with Application.ScreenUpdating = False Application.Calculation = xlCalculationManual and end with Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic -- -John Please rate when your question is answered to help us and others know what is helpful. "QuietMan" wrote: I'm running a macro that pull data from excel file #1 to Excel File #2 The Macro is in excel file #2 it uses the sumif and offset formulas in combination with a ranged name linked to excel file #1 (there are 43 worksheets of informatiuon in this file, I need from 19 of these worksheets) Formula: [DataArray = range referrenced in Excel file #1, changes based on column in excel file #2] ActiveCell.FormulaR1C1 = _ "=SUMIF(" & DataArray & ",RC1,OFFSET(" & DataArray & ",0,R1C,ROWS(" & DataArray & "),1))" Excel file #2 has 228 column of data and 1000 rows I use a nested For Next loop to go through the full range and add the correct formula to each cell. the copy the full range of data then paste value it before turning back on calculations. This works fine but I have to do this for five sheet in the woorbook, and after running the macro excel becomes slow and slower does any on have a better way of doinmg this that would be faster? Thanks -- Helping Is always a good thing |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Speeding up execution of a Macro
Turn calc off, loop through the range by column, add the formular1c1 in one
fell swoop, turn calc on. No need to loop through each cell in the range by column. -- Tim Zych SF, CA "QuietMan" wrote in message ... I'm running a macro that pull data from excel file #1 to Excel File #2 The Macro is in excel file #2 it uses the sumif and offset formulas in combination with a ranged name linked to excel file #1 (there are 43 worksheets of informatiuon in this file, I need from 19 of these worksheets) Formula: [DataArray = range referrenced in Excel file #1, changes based on column in excel file #2] ActiveCell.FormulaR1C1 = _ "=SUMIF(" & DataArray & ",RC1,OFFSET(" & DataArray & ",0,R1C,ROWS(" & DataArray & "),1))" Excel file #2 has 228 column of data and 1000 rows I use a nested For Next loop to go through the full range and add the correct formula to each cell. the copy the full range of data then paste value it before turning back on calculations. This works fine but I have to do this for five sheet in the woorbook, and after running the macro excel becomes slow and slower does any on have a better way of doinmg this that would be faster? Thanks -- Helping Is always a good thing |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Speeding up execution of a Macro
I can easily determine the range to apply the formula, but how do I apply
formularr1c1 to an entire range? Thanks -- Helping Is always a good thing "Tim Zych" wrote: Turn calc off, loop through the range by column, add the formular1c1 in one fell swoop, turn calc on. No need to loop through each cell in the range by column. -- Tim Zych SF, CA "QuietMan" wrote in message ... I'm running a macro that pull data from excel file #1 to Excel File #2 The Macro is in excel file #2 it uses the sumif and offset formulas in combination with a ranged name linked to excel file #1 (there are 43 worksheets of informatiuon in this file, I need from 19 of these worksheets) Formula: [DataArray = range referrenced in Excel file #1, changes based on column in excel file #2] ActiveCell.FormulaR1C1 = _ "=SUMIF(" & DataArray & ",RC1,OFFSET(" & DataArray & ",0,R1C,ROWS(" & DataArray & "),1))" Excel file #2 has 228 column of data and 1000 rows I use a nested For Next loop to go through the full range and add the correct formula to each cell. the copy the full range of data then paste value it before turning back on calculations. This works fine but I have to do this for five sheet in the woorbook, and after running the macro excel becomes slow and slower does any on have a better way of doinmg this that would be faster? Thanks -- Helping Is always a good thing |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Speeding up execution of a Macro
Same as the cell.
rng.FormulaR1C1 = "<R1C1formula" That's one of the powers of R1C1 formulas. -- Tim Zych SF, CA "QuietMan" wrote in message ... I can easily determine the range to apply the formula, but how do I apply formularr1c1 to an entire range? Thanks -- Helping Is always a good thing "Tim Zych" wrote: Turn calc off, loop through the range by column, add the formular1c1 in one fell swoop, turn calc on. No need to loop through each cell in the range by column. -- Tim Zych SF, CA "QuietMan" wrote in message ... I'm running a macro that pull data from excel file #1 to Excel File #2 The Macro is in excel file #2 it uses the sumif and offset formulas in combination with a ranged name linked to excel file #1 (there are 43 worksheets of informatiuon in this file, I need from 19 of these worksheets) Formula: [DataArray = range referrenced in Excel file #1, changes based on column in excel file #2] ActiveCell.FormulaR1C1 = _ "=SUMIF(" & DataArray & ",RC1,OFFSET(" & DataArray & ",0,R1C,ROWS(" & DataArray & "),1))" Excel file #2 has 228 column of data and 1000 rows I use a nested For Next loop to go through the full range and add the correct formula to each cell. the copy the full range of data then paste value it before turning back on calculations. This works fine but I have to do this for five sheet in the woorbook, and after running the macro excel becomes slow and slower does any on have a better way of doinmg this that would be faster? Thanks -- Helping Is always a good thing |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Speeding up Macro | Excel Programming | |||
Speeding Up Macro in VBA | Excel Programming | |||
Help with speeding up vlookup macro | Excel Programming | |||
Speeding up a Data Validation macro | Excel Programming | |||
speeding up a macro | Excel Programming |