Macro to copy formulas down one row at a time to prevent Excel crashing

Hi,

Originally Posted by CRMORE

Hi,

Still trying to work this out........

_ It sounds like you want to do something very basic and if time is not an issue then there are probably an infinite number of ways to do it. I recently built up formulas using a combination of actual written string parts of the formula along with making up the cell references from variables containing the row and column indicies which I obtained from a code bit. Then I stuck em in either one at a time or put a few of them in and Array then pasted that in in one go. Seemed simple enough once I sussed out how.....
http://www.mrexcel.com/forum/excel-q...ordinates.html

_ If you give screenshot that i can copy ( NOT An image ) ( see my signature ) or upload a file WITH REDUCED DATA and tell me exactly what formula you want to paste in where, then I will give it a go for you ( tomorrow)
Alan

EDIT: This Thread is ( almost ) a Duplicate of this
http://www.excelforum.com/excel-gene...-crashing.html

- .. so for the next time put a link at least in both Thrreads so that people do not do duplicate work for you, or make duplicate suggestions..

Hi Alex,

Originally Posted by CRMORE

... I've attached a sample sheet which just has the first 30 lines of data. Formulas I want to copy down (one by one) are highlighted in yellow. .....

OK I will take a look ( may have to be tomorrow now )

BTW. You drag those formulas down, so the row numbers in them increase , Yes ? Or do you just want the exact formulas in every cell going down

Alan

P.s. 1
The other Thread thing was no big deal, just sometimes a link to one similar or a bit related can help get the full picture

P.s. 2
Also no big deal, but when replying with quote, chop out a bit like I did with yours - helps keep the thread a bit less cluttered and easier to read.

Try this:

Please Login or Register  to view this content.

or one at a time:

Please Login or Register  to view this content.

Hi Alex,
_ The code from XLAdept is definitely the one You should go for. ( He knows wot he is doing )
_ I assumed ( stupidly ) that the basic line by line Pasting you had tried
_ So i started my somewhat more complicated Method. As I started I thought I would never the less finish. You have an alternative way of doing it therefore. I cannot immediately see any advantage over the method from XLAdept. But it may give you some wild ideas to you sometime in the future when you take your project further. ( And i certainly had fun practicing some pretty complicated Formulas!!!! )

_ As I then saw your final request for pasting out just the values, I modified the code and in fact did two versions.
_ Both codes write in a loop a 14 “column” 1 Dimensional Array your Formulas. These are then Pasted out to the sheet line by line. So basically doing just what XLAdept’s code does but in a very complicated way!! Also at each line the values given by the formulas just pasted out are collected into another Temporary 14 “column” 1 Dimensional Array, this time with the values in it rather than the Formulas.
_ Then: ..

_ Code 1 Pastes back out at each line the values , so overwriting the formulas.

_ Code 2 Instead of pasting out a row of values each time, the Values for each row are collected in a large Output Array. Then just before the end of the program these values are pasted out in one go. ( I believe your problems arose when Pasting out all the formulas in One go. This problem should not arise when posting all values in one go )

_ My two Codes I put here:
http://www.excelforum.com/developmen...ml#post4201247
and here:
http://www.excelforum.com/developmen...ml#post4201248
_ ...... , as they are a bit big to cram in here: ( I checked them with your Uploaded file and they appear to give the correct results. )


Alan

P.s. 1 ) I expect the Modification to XLAdept’s code for pasting out the values is very simple. You would add a couple of code lines to copy the 14 columns just pasted and then re paste out using xlValues rather than xlPasteFormulas. But i leave that to XLAdept to advise fully.

P.s.2 ) Probably the best way to do your requirement might be to copy all your data in one go in an Input Array, do all your calculations internally , put the calculated values ( as i have done in second Code ) in a large Output Array and then paste that out in one go.. Not difficult, but a lot of work again writing out a lot of formulas...... !


P.s.3. )
Here for example the formulas given by either of my codes for line 3
Using Excel 2007

Row\Col	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD
3	=IFERROR(INDEX($M$2:$M$2,SUMPRODUCT(MAX(ROW($G$2:$G$2)*($G$3=$G$2:$G$2))-1)),0)	=IFERROR(INDEX($M$2:$M$2,SUMPRODUCT(MAX(ROW($G$2:$G$2)*($C$3=$C$2:$C$2)*($G$3=$G$2:$G$2))-1)),0)	=IFERROR(INDEX($M$2:$M$2,SUMPRODUCT(MAX(ROW($G$2:$G$2)*($D$3=$D$2:$D$2)*($G$3=$G$2:$G$2))-1)),0)	=IFERROR(INDEX(INDIRECT($AD$3,TRUE),SUMPRODUCT(MAX(ROW(INDIRECT($AC$3,TRUE))*($G$3=INDIRECT($AC$3,TRUE)))-1)),0)	=IFERROR(INDEX(INDIRECT($AD$3,TRUE),SUMPRODUCT(MAX(ROW(INDIRECT($AC$3,TRUE))*($C$3=INDIRECT($AA$3,TRUE))*($G$3=INDIRECT($AC$3,TRUE)))-1)),0)	=IFERROR(INDEX(INDIRECT($AD$3,TRUE),SUMPRODUCT(MAX(ROW(INDIRECT($AC$3,TRUE))*($C$3=INDIRECT($AB$3,TRUE))*($G$3=INDIRECT($AC$3,TRUE)))-1)),0)	=IF($Q$3=1,5,IF($Q$3=2,3,IF($Q$3=3,2,IF($Q$3=4,1))))+IF(OR($R$3=1,$U$3=1),1)+IF(OR($S$3=1,$V$3=1),2)+IF($T$3=1,5,IF($T$3=2,3,IF($T$3=3,2,IF($T$3=4,1))))		=$Y$2+1	=IFERROR(INDEX($Y$2:$Y$2,SUMPRODUCT(MAX(ROW($G$2:$G$2)*($G$3=$G$2:$G$2))-1))-1,0)	="$C$2:$C"&$Z$3	="$D$2:$D"&$Z$3	="$G$2:$G"&$Z$3	="$M$2:$M"&$Z$3

Sheet1

_...
And the final pasted valies
Using Excel 2007

Row\Col	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD
3	0	0	0	0	0	0	0		3	0	$C$2:$C0	$D$2:$D0	$G$2:$G0	$M$2:$M0

Sheet1

Hi CR,

Try this:

Please Login or Register  to view this content.

Hi Alex
_ I’ll guess from what you have said so far that you are very new to VBA so forgive me if this is too obvious....

_ Often people will use an abbreviation for last row like lr or LR or LastRow etc. So I think straight away you can see from xladept’s and my codes where the Last row is defined ( or rather determined )

_ I kept my determination of lr for simplicity ( and for ease of comparison with xladept’s code, ) the same as in xladept’s. He uses a very often useful and quick way which looks at UsedRange which is actually the “box” which extends in the sheet to cover any cell you ever used in that sheet. Provided you started then at ( or ever had anything in ) the first row and first column, then the .Rows property applied to the UsedRange range Object will give you your final ( ever used ) row.
_ There can be subtle problems with UsedRange
http://www.mrexcel.com/forum/excel-q...ml#post4222780
http://www.excelforum.com/excel-new-...ml#post4137312
_ I have therefore got out of the habit of using it. I would use a method that looks in a particular column and finds the last cell with something in it in that column.
_ So I redid my codes for you and used the method I usually use. In those codes I determine the last row from looking in your first ( Date ) Column

_ Having done that it follows on nicely to answer your question:

Originally Posted by CRMORE

.... 1. Is it possible to specify for the macro to continue from the next blank row (so if I've already filled to e.g. row 100,000 but had to stop the macro, next time it starts it goes to row 100,001 .....

_ Our codes loop
For row 3 to LastRow.

_ So in my code i have a new variable for start row, sr, and repeat the modified line but this time for finding the last cell with something in it in column Q ( where your formulas or values from those formulas are )
_ Then the Loop variables are changed appropriately so that i loop
For sr to LastRow

_ that is all the modification you then need for my first code to do what you want as regards starting at the next blank row ( My second code needed some minor modifications to the dimension of the Array for Values Output and Top left corner output cell for where that Array is finally outputed to the sheet )

_ To get the code to stop where you want then there are two possibilities with my codes.
Etiher
_ Only put dates in up to where you want the next lot of formulas to be put in.
Or
_ Simply change the bit in the code which determines lr to
Let lr = 200000 , or whatever the next row number is you want to go up to

Here are my modified codes
Here
http://www.excelforum.com/developmen...ml#post4203381
and here
http://www.excelforum.com/developmen...ml#post4203382


_ .............................

_ For xladept’s code the change for LastRow is the same. But for the start Row it would be a bit different as the code currently ends up in values ( as do mine ) so his code has no original formulas to copy ( For mine that is irrelevant as i make the formulas internally ). He would probably change the code to replace the last two lines to
Range("Q3:AD" & LR).Copy
Range("Q3").PasteSpecial xlPasteValues
Hence leaving formulas in second row for later copying. He would also I expect do something similar to me to determine a start row and use that determined variable value rather than 3 as the start of his Loop... and then maybe something of the form
Range("Q " & sr & ":AD" & LR).Copy
Range("Q " & sr & "").PasteSpecial xlPasteValues
So as to only copy and convert to values the last Range considered.

Alan

Hi CR,

Thanks for the rep!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

@ Alex

Did you catch my post #13 ( you might have missed that on your cell phone ! )
Alan