Using Variant Object to copy formulas across large amount of data

[jdaywalt]

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!!

 

[ChrisBM]

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:

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

Hope this helps,
Chris

 

[jdaywalt]

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]

If you have huge amounts of data you may simply need to copy one column of formulas at a time.

 

[iliauk]

Do you mean something like this?

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]

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]

Aha ok. Maybe this

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