Using VBA to copy large number of formulas efficiently

[jdaywalt]

Hoping for some VBA assistance. I have a worksheet that contains a master set of formulas in cells AE2:AZ2. Users copy a data file into cells AA5:AD-- (ending row varies). Once the data is copied, the user clicks a button to 'refresh formulas', which then copies the master formulas from row 2 into the data rows AE5:AZ--. It also then replaces the live formulas with values. This macro works fine for most data files imported, however there are a few data files that contain too much data & when the user attempts to run the 'refresh formula' macro, Excel comes back with a "not enough resources" error & the macro bombs. Below is the code I am currently using for this process. Is there a more efficient way to construct my code to avoid the resource error? I've heard there is a VBA procedure that copies data out into array, then pulls it back into Excel (I think??), but I am unfamiliar with this process. Any help would be MUCH appreciated!!

Sub Copy_Formulas()​

Dim Numrecords​

Numrecords = Range("AE1").Value 'cell AE1 is a formula that recognizes #of data records copied into the file. This value is used for multiple purposes.​

​

Range("AE2:AZ2").Copy Range("AE5:AZ" & CStr(Numrecords + 4))​

Range("AE5:AZ" & CStr(Numrecords + 4)).Value = Range("AE5:AZ" & CStr(Numrecords + 4)).Value​

Application.CutCopyMode = False​

End Sub​

 

[JLGWhiz]

Try turning calculation off, do the copy, turn calc back on.

Sub Copy_Formulas()
Dim Numrecords
Numrecords = Range("AE1").Value 'cell AE1 is a formula that recognizes #of data records copied into the file. This value is used for multiple purposes.
Application.Calculation = xlCalculationManual
Range("AE2:AZ2").Copy Range("AE5:AZ" & CStr(Numrecords + 4))
Range("AE5:AZ" & CStr(Numrecords + 4)).Value = Range("AE5:AZ" & CStr(Numrecords + 4)).Value
Application.Calculation = xlCalculaionAutomatic
Application.CutCopyMode = False
End Sub

 

[jdaywalt]

Thanks for the response. Unfortunately, that won't work. The whole macro involves copying the formula---then directly after it calculates---converts the results to values. I am not doing a series of independent formula copies, which may be aided by your suggested approach. The full copy occurs at one time and it's really the only thing the macro is doing. I'm looking for a process I'm only vaguely familiar with that uses a variant object(?) to temporarily copy the range out to memory 'in bulk', apply formulas, then paste the resulting values back 'in bulk' to the spreadsheet. Are you familiar with this process? I've heard it makes a big difference in response times when executing.

 

[Ragnar1211]

Try this .... i think the calculations are not finished yet:

[INDENT]Sub Copy_Formulas()[/INDENT]
[INDENT=2]Dim Numrecords[/INDENT]
[INDENT=2]Numrecords = Range("AE1").Value  [COLOR=#ff0000]'cell AE1 is a formula that recognizes #of data records copied into the file.  This value is used for multiple purposes.[/COLOR][/INDENT]
[INDENT=2]        Range("AE2:AZ2").Copy Range("AE5:AZ" & CStr(Numrecords + 4))

Do While Not Application.CalculationState = xlDone
    Application.Wait (Now + TimeValue("0:00:05"))
    DoEvents
Loop
Range("AE5:AZ" & CStr(Numrecords + 4)).Value = Range("AE5:AZ" & CStr(Numrecords + 4)).Value
[/INDENT]
[INDENT=2]        Application.CutCopyMode = False[/INDENT]
[INDENT]End Sub
[/INDENT]

 

[JLGWhiz]

Thanks for the response. Unfortunately, that won't work. The whole macro involves copying the formula---then directly after it calculates---converts the results to values. I am not doing a series of independent formula copies, which may be aided by your suggested approach. The full copy occurs at one time and it's really the only thing the macro is doing. I'm looking for a process I'm only vaguely familiar with that uses a variant object(?) to temporarily copy the range out to memory 'in bulk', apply formulas, then paste the resulting values back 'in bulk' to the spreadsheet. Are you familiar with this process? I've heard it makes a big difference in response times when executing.

Yes, you are correct that it would not work the way I had it. But if we move the turn on to automatic to a line before the change to value, it might work. I am sure that it is the calculations that are causing the problem by trying to combine the paste and calculate actions. If you can get the paste completed and then calculate it should work OK.

Sub Copy_Formulas()
Dim Numrecords
Numrecords = Range("AE1").Value 'cell AE1 is a formula that recognizes #of data records copied into the file. This value is used for multiple purposes.
Application.Calculation = xlCalculationManual
Range("AE2:AZ2").Copy Range("AE5:AZ" & CStr(Numrecords + 4))
Application.Calculation = xlCalculaionAutomatic
Range("AE5:AZ" & CStr(Numrecords + 4)).Value = Range("AE5:AZ" & CStr(Numrecords + 4)).Value
Application.CutCopyMode = False
End Sub

 

[jdaywalt]

Sorry, tried it but did not make a difference. Thanks anyway!

 

[JLGWhiz]

Sorry, tried it but did not make a difference. Thanks anyway!

Then the only other solution I can think of is to break your larger input files into smaller packages. That would mean counting the records beforehand and if they exceed a certain count, create two files from one for input to the processing file.
Regards, JLG

 

[Ragnar1211]

If you want to use the array approach you will need to post the calculations you need performed on each cell.

You can calculate the values in memory and populate the cells at the end.