LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 109
Default 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
 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Speeding up Macro jayklmno Excel Programming 4 October 11th 06 03:54 AM
Speeding Up Macro in VBA VexedFist[_2_] Excel Programming 4 October 4th 06 09:13 PM
Help with speeding up vlookup macro [email protected] Excel Programming 2 July 20th 06 05:59 PM
Speeding up a Data Validation macro Ryan[_11_] Excel Programming 2 June 28th 05 03:29 AM
speeding up a macro Brenda[_5_] Excel Programming 4 August 21st 03 12:56 AM


All times are GMT +1. The time now is 07:03 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"