VBA Variant Array Copy Down Formulas

Redhawk1

New Member
Joined
Jun 1, 2018
Messages
5
I'm new to Variant Arrays and everyone says they are a game-changer so I'm trying to get this to work! Spent a couple days reading messages and just can't seem to grasp what I need to do.

  • Row 5 has formulas (though some cells are blank)
  • Starting on Row 10 is where I'd like to copy the formulas to (even row 5 has a formula). I need them to be relative references - see attached sheet for example
  • All formulas refer back to column 1 (this is maybe confusing but there is an API connection that drives off the value in column 1 - I tried to simplify this).
I want to do this through a variant array (if I'm even saying that the right way) because a basic loop through 100k rows and 50 columns froze my machine.

Here the code I'm trying to work with but I can't even get past the subscript out of range for the first part of the loop...

Sub Array_Formula_Practice()
'Variants
Dim ArrTopFormulas As Variant, ArrData As Variant, ArrBodyFormulas As Variant
Dim R As Long, C As Long, i As Long

'LastRow/Column
Dim LastRow As Long, LastCol As Long

With Sheets("Sheet1")
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row

ArrTopFormulas = .Range(.Cells(5, 2), .Cells(5, LastCol)).Formula
ArrBodyFormulas = .Range(.Cells(10, 2), .Cells(LastRow, LastCol))

For C = 2 To LastCol
For R = 10 To LastRow
If Left(ArrTopFormulas(5, C), 1) = "=" Then ArrBodyFormulas(R, C) = ArrTopFormulas(5, C)
Next R
Next C

' Now put ArrFormulas back where it came from
.Range(.Cells(10, 2), .Cells(LastRow, LastCol)).Formula = ArrBodyFormulas
End With
End Sub
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Redhawk1

New Member
Joined
Jun 1, 2018
Messages
5
I've botched this post and can't edit or delete :(

Formula example in Cells(5, 2) = $A5&B$1
That is what needs to be copied down starting in row 10 of the respective column

Thanks and apologies for jumping the gun with a not-so clear post
 

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
2,773
Office Version
  1. 2010
Platform
  1. Windows
The reason you are getting the subscript out of range is because you have loaded arrays which start at columnn 2 and row 10 and then you limited the loops by the last row and last column these number will be beyond the bounds of the array because you loaded NOT starting at 1.
I often load the array starting at cells(1,1) (A1) and then starting the loop control at 2 to lastcol and 10 to lastrow. This is very easy way because the loop contorl variable ties up with the row/column, an alternative is to do what I have done to update your code to load arrays starting NOT at A1 but then looping from 1 to the upper bound of the array. In this case the loop variable is NOT equal to the column or row number which can make it difficult depending on what you are trying to do.
I don't think this is going to do what you want it to do. When you copy a formula down a range in EXCEL, EXCEL automatically changes the references row by row and column by column so the references are the same realtive positions to the cell written. ( Very helpful) the code that you have written pickup the formulae in ROW 5 , converts it into a text string ( in the array) and then writes exactly the same text string back to every cell in that column i.i is DOESN't change the cell references. If you want excel to alter the references in the formula the easiest way is to use the range copy method:
VBA Code:
Worksheets("Sheet1").Range("B2:B6").Copy _
    Destination:=Worksheets("Sheet1").Range("C2")
It is possible to use varaint arrays to write specific formula into the cells but is a lot more complicated because you need to make all the alterations to references a you loop through this is a very simple example of how to do that:
VBA Code:
Sub test2()
inarr = Range("F1:G8")
For i = 2 To 8
 inarr(i, 1) = i
 inarr(i, 2) = "=F" & i & "/2"
Next i
 Range("F1:G8") = inarr
the updated code to correct your error:
With Sheets("Sheet1")
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row

ArrTopFormulas = .Range(.Cells(5, 2), .Cells(5, LastCol)).Formula ' You have loaded columns 1 to lastcol so the array is dimensions = 1 to lastcol -1 NOT lastcol
ArrBodyFormulas = .Range(.Cells(10, 2), .Cells(LastRow, LastCol)) ' you have loaded this for column 2 row 10 so the dimensions are: 1 to lastrow -9 row by 1 to lastcol -1 columns


For C = 1 To UBound(ArrTopFormulas, 2) ' this automatically bounds the loop to the size of the column array starting at 1 NOTE the 2 which says it is columns
For R = 1 To UBound(Artopformulas, 1) '' this automatically bounds the loop to the size of the rows array starting at 1 NOTE the 1 which says it is rows
If Left(ArrTopFormulas(5, C), 1) = "=" Then ArrBodyFormulas(R, C) = ArrTopFormulas(5, C)
Next R
Next C

' Now put ArrFormulas back where it came from
.Range(.Cells(10, 2), .Cells(LastRow, LastCol)).Formula = ArrBodyFormulas
End With
End Sub
 

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
2,773
Office Version
  1. 2010
Platform
  1. Windows
I realised I made an error in my code try this correction:
VBA Code:
With Sheets("Sheet1")
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row

ArrTopFormulas = .Range(.Cells(5, 2), .Cells(5, LastCol)).Formula ' You have loaded columns 1 to lastcol so the array is dimensions = 1 to lastcol -1 NOT lastcol
ArrBodyFormulas = .Range(.Cells(10, 2), .Cells(LastRow, LastCol)) ' you have loaded this for column 2 row 10 so the dimensions are: 1 to lastrow -9 row by 1 to lastcol -1 columns


For C = 1 To UBound(ArrTopFormulas, 2) ' this automatically bounds the loop to the size of the column array starting at 1 NOTE the 2 which says it is columns
For R = 1 To UBound(ArBodyformulas, 1)  ' NOTE CHANGE TO THIS LINE
If Left(ArrTopFormulas(5, C), 1) = "=" Then ArrBodyFormulas(R, C) = ArrTopFormulas(5, C)
Next R
Next C

' Now put ArrFormulas back where it came from
.Range(.Cells(10, 2), .Cells(LastRow, LastCol)).Formula = ArrBodyFormulas
End With
End Sub
 

Forum statistics

Threads
1,182,099
Messages
5,933,642
Members
436,902
Latest member
Ameratsu

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