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

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

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
35,517
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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,360
Messages
5,528,233
Members
409,809
Latest member
VICKRAM

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top