Macro problem - copying existing formula down column

**JerryMcFarts** · 10-27-2008, 10:26 AM

O.K. I will try to explain this as best as possible.

I have two excel files that I am dealing with. The first excel file is populated by a database. The other excel file uses the data from that first excel file.

In the 2nd excel file I have formulas in the cells that grab the information I need. (I compare the dates, then other criteria to grab the information).

I have a macro in the 2nd excel file that pulls down the formulas to the current day, when you run it.

I left that location and now I am in a different state. (It worked when I was there locally) I get a call from a coworker saying that when he runs the macro the cell formula jumps and doesn't progress by +1. So one cell will be =B1 then the next cell under will =B4. So I open the file up to take a look at the problem. But I do not see what he sees.

I see =B1 and then the next cell =B2. We talk on the phone and we are seeing totally different things.

Why would we be seeing different things?

**JerryMcFarts** · 10-27-2008, 11:30 AM

Went through the trouble shooting again with the coworker. I can see the problem now.

I can only speculate that he manually fixed it and I opened it while it was fixed and then he ran the macro again to create the problem see the problem..

I am looking at my macro, but i still ahve the problem of why would it jump +1 on the copy down function.. but I will post later after looking at it

**JerryMcFarts** · 10-27-2008, 01:03 PM

So.. I am a bit confused.

in short my macro is:

Please Login or Register  to view this content.

This macro just copies the formulas that are in the existing cell down the column to the next cell. So it should be:
Cell 1=B1
Cell 2=B2
cell 3=B3

But when my coworker runs the macro the formulas are not continuous, they jump reference cells. I.E.
=B1
=B5
=B6

I delete the cells and re run it and it does it correctly.. What can I do to correct this problem?

**JerryMcFarts** · 10-27-2008, 03:43 PM

This might be helpful or key. I am having the issue with the "excel cannot complete this task with available resources." error. I have seen this on these boards and I believe it is because I am referencing more than 16,000 cells by a formula. So I am working on fixing that problem. Could that problem create this problem?

http://support.microsoft.com/kb/214342/en-us

**JerryMcFarts** · 10-29-2008, 05:14 PM

So I couldn't figure out why it was randomly skipping down my formula and I assumed that it was the available resources error. I slowly took out formula by formula to find out the culprit.

Turns out this formula is giving me the error:

Please Login or Register  to view this content.

If I lower the range down to 5000 it works but it doesn't grab the data that I want. Is there a better way of grabbing this data?

Thanks,

**JerryMcFarts** · 11-11-2008, 10:34 AM

The problem is that excel can't handle my excel formula that pulls more that 16,000 cells from another .xls.

I have pointed my problem to IT and I have requested that the Oracle database that I am getting data from do the calculation instead of my Excel. This is good and bad because the Excel that I want plant engineers to use wont have control of the calculation. I am waiting for this to happen but im 100% this will fix my woes.

Thanks,

**JerryMcFarts** · 11-24-2008, 10:35 AM

So I had the IT department use my calculations in the database rather than the excel this cleared up my problem with "excel cannot complete this task with available resources."

But my original problem exists: I have a Macro that copies down rows of formulas from the row above but it doesn't copy it down correctly (Randomly).

what other info can I include?