Using Variant Object to copy formulas across large amount of data

jdaywalt

Board Regular
Joined
Jul 18, 2012
Messages
63
I posted a question yesterday and was not able to get a successful solution to my problem. Upon further research, I think my solution lies in using a 'Variant Object' to copy a formula across a large data range "in bulk" to Excel memory---then when finished, copy back into the worksheet. Is anyone familiar with this process that can help me? Here are the specifics:

Formula that needs to be copied is located in range: AE2:AZ2
Range it needs to be copied against: AE5:AZ-- *ending row depends on how much data exists in file. Data is populated in range: A5:AD--

So basically, I'd like to create this variant object to copy the formula into all "data" rows based on the amount of data in the file. (NOTE: The formulas depend upon the data to get the result, so assume the data must also be copied into this variant object so the calculations will work correctly??) Any help would be greatly appreciated!!
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

ChrisBM

Board Regular
Joined
Sep 22, 2014
Messages
215
Maybe I am misunderstanding the issue but if you are just copying a set of formulae from one set of cells and put them in another a simple way would be:

Code:
Range("AE2:AZ2").Copy
Range("AE5:AZ" & Range("AE5").End(xlDown).Row).PasteSpecial Paste:=xlPasteFormulas

Hope this helps,
Chris
 

jdaywalt

Board Regular
Joined
Jul 18, 2012
Messages
63
I actually already have that type of code in my file, and it works great except when there is a large amount of data in the file. I get an Excel 'out of resources' error and the macro crashes. I am seeking use of the Variant Object as a means to do this more efficiently and avoid the errors. I have seen this type of code in other files (not developed by me), and it works extremely well. I have never used a 'Variant Object' before, and don't know how to apply it to my specific 'copy formula' procedure.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
38,820
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
If you have huge amounts of data you may simply need to copy one column of formulas at a time.
 

iliauk

Board Regular
Joined
Jun 3, 2014
Messages
163

ADVERTISEMENT

Do you mean something like this?

Code:
Dim Container() As Variant
Dim rng1 As Range
Dim rng2 As Range
Dim cell As Range
Dim counter As Integer

Application.Calculation = xlCalculateManual
Set rng1 = Range("AE2:AZ2")
counter = 1
'Step1: Fill array with data:
For Each cell In rng1
    ReDim Preserve Container(1 To counter)
    Container(counter) = cell.Formula
    counter = counter + 1
Next cell


Set rng2 = Range("AE5:AZ5")
counter = 1
For Each cell In rng2
    cell.Formula = Container(counter)
    counter = counter + 1
Next cell
Application.Calculation = xlCalculateAutomatic
 

jdaywalt

Board Regular
Joined
Jul 18, 2012
Messages
63
iliauk: Yes, I believe I need something like this! Before I try to incorporate into my file to test, I'm confused by one thing. In the section where you Set rng2 = Range("AE5:AZ5"), will that only copy the formula into that one row? My challenge is that I need to copy the formula down for multiple rows. The number of rows is based upon how much data exists in range A5:AD-- (ending row TBD). Does that make sense?
 

iliauk

Board Regular
Joined
Jun 3, 2014
Messages
163
Aha ok. Maybe this

Code:
Sub VariantCopy()
Dim Container() As Variant
Dim rng1 As Range
Dim rng2 As Range
Dim cell As Range
Dim counter1 As Integer
Dim counter2 As Integer
Dim tbd As Integer

tbd = 15
Application.Calculation = xlCalculationAutomatic

Set rng1 = Range("AE2:AZ2")
counter1 = 1
'Step1: Fill array with data:
For Each cell In rng1
    ReDim Preserve Container(1 To counter1)
    Container(counter1) = cell.Formula
    counter1 = counter1 + 1
Next cell

counter1 = counter1 - 1

Set rng2 = Range("AE5:AZ" & tbd)
counter2 = 1
For Each cell In rng2
    cell.Formula = Container(counter2)
        If counter2 < counter1 Then
        counter2 = counter2 + 1
        Else
        counter2 = 1
        End If
    Next cell
End Sub
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,651
Messages
5,838,584
Members
430,557
Latest member
MK15

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top