How to copy formulas from row and paste as formula into multiple rows using Array

0
votes

I want to copy the formulas from one row and paste it onto number of rows depending upon how many rows copied from source-file. I am copying formulas from one row into array and assigning the same array to all the next rows using for loop. I am either getting values pasted or same formulas for example =B1+c1 in to all rows instead of incremental cell numbers like =B99+C99

So far I tried assigning .formula to object and assigning back from array to FormulaArray - this is applying {} to the formula and pasting same formula in each row. There are rows over 200K. -formula to array and array to formula - this is failing -value to array and array to value - Obviously this gives just values.

vArray2 = Wb1.Worksheets("Gains Data").Range("AZ2:CJ2").Formula 
Dim i1 As Long
For i1 = Row2 To Row3
  Wb1.Sheets("Gains Data").Range(Cells(i1, Col1), Cells(i1, 
  Col2)).FormulaArray = vArray2 
Next i1

How to preserve the formula but still get the formulas applied to the incremental cell values. Instead of getting =B2 + C2 into all the rows, I want =B10100 + C10100

1
excelvba

1 Answers

0
votes

.FormulaArray is for array formulas, hence the {}. You need .Formula, and you don't need a loop. You can write the formulas to the entire range with one line, and Excel will update the row references since you have relative references.

Also note that this can be simplified with With...End With, and note the period . in front of each instance of Cells. 

With Wb1.Worksheets("Gains Data")
    vArray2 = .Range("AZ2:CJ2").Formula
    .Range(.Cells(Row2, Col1), .Cells(Row3, Col2)).Formula = vArray2
End With