VBA Variant Array Copy Down Formulas

[Redhawk1]

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

 

[Redhawk1]

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]

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:

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:

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]

I realised I made an error in my code try this correction:

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