Copying Down Formulas

[frog0234]

I am using Excel 2007 and have a spread sheet of 800 columns and 163,000 rows. In each cell of the first row are equations similar to this: =VLOOKUP(F4,Sheet2!$A$1:$ADT$571,H$3)-VLOOKUP(G4,Sheet2!$A$1:$ADT$571,H$3)

I am trying to copy down this row to row 163,000 and am only able to get to around row 15,000 before I get the message that I have run out of system resources. I have tried turning off auto calculations in the cells. What else can I do to populate the cells with the formulas?

Thanks.

[RHCPgergo]

Hey,
first select the range where you want to formula to be pasted, then run this macro.
I assumed that your selection starts in row 4. If not, tell me and I will alter the code.

Please Login or Register  to view this content.

[Pepe Le Mokko]

If you could post a small sample of your sheet so we can see what you are trying to do, it would help us a lot.
I also thing an INDEX/MATCH combination needs less resources

[frog0234]

Here is the spreadsheet I am working with. I tried the VBA code and am getting an error.

Any other ideas?

[frog0234]

Here is the file. I have removed alot of the data from Sheets 1 & 2 to allow for upload. But again, I am trying to drag down the first row of formulas about 163,000 rows. The data table in Sheet 2 has 571 rows of 800 columns.

[frog0234]

Any thoughts on this?

[RHCPgergo]

Hi again,

how about this one?
http://spreadsheets.about.com/od/tip...30filldown.htm

Applied to your case:
1. highlight your first row of data (from column C to the end)
2. press F5 (brings up the "Go to" dialog box)
3. type in the bottom right cell address of the range where you want to apply the formula (should be something like this: ADT163000)
4. click OK while holding shift (now all that huge range should be highlighted)
5. press Ctrl + D
6. formula should be applied everywhere.

Tell me if it works please.

[frog0234]

Neat trick, but it doesn't work for this situation. I first get the warning notice of "large data operation", after I click ok I get "Excel cannot complete this task with available resources. Choose less data or close other applications"

Other ideas?

[RHCPgergo]

Wow.

How about applying it in smaller chunks? Like for every ten thousand row?
Also, once you have the correct data in the cells, you can use Copy-->Paste Special-->Values, so formulas disappear. Makes the file size smaller.

[jleonard5106]

Try this code. i have also attached the file.

I created a copy of sheet 1 and named it test.

Please Login or Register  to view this content.

[frog0234]

The VBA code works to about row 7000 and then I get an error stating that there are no more system resources available to continue.

Other ideas?

[jleonard5106]

I am still working on it.

are the formulas important or just number?

[jleonard5106]

Try this Code

Took away the vlookups and just used simple subtractions

Please Login or Register  to view this content.

[frog0234]

Wow, I really appreciate your work. I don't use message boards often, but it is nice to see people so willing to help others especially with the complexity to your VBA code. I started to run it, but it is taking a long time (which is ok), I will have to run it when I can walk away from the computer for a bit. I will let you know the final outcome. Thanks again. I will follow up soon.