CODEWITHSUNDEEP
vba
Eli3
Eli3

Reputation: 111

Copy Formula to a range

Would like to copy a formula from a cell to a range by using variables. It works fine with xlPasteFormulas but want to avoid it. This formula used wrong references so endresult is wrong:

With ActiveSheet
    .Range(.Cells(rowStarWs1, colAct), .Cells(rowLast, colAct)).Formula = .Cells(rowSuch2Ws1, colAct).Formula 
End With

Example: Formula is saved in cell C3 (e.g. A3*B3)

Would like to paste the formula to range C10:C13. Range is given by my variables.

Result should look like below (Yellow cells)

enter image description here

Upvotes: 2

Views: 13261

Answers (2)

Variatus
Variatus

Reputation: 14383

You might use FillDown for this purpose.

Cells(10, 3).Formula = Cells(3, 3).FormulaR1C1
Range("C10:C14").FillDown

FillDown copies the formula from the first cell of the defined range to all its cells. Therefore the first line of code fills that first cell. Assigning the R1C1 format to the Formula property changes the cells referenced in the formula without using the R1C1 format in the output.

Upvotes: 0

Scott Holtzman
Scott Holtzman

Reputation: 27269

I find using R1C1 notation very helpful when moving formulas around, especially in simple cases like the one you illustrated. More consideration is needed for more complex cases.

Sub f()

    Dim f As String
    f = Range("C3").FormulaR1C1
    
    Range("C10:C13").FormulaR1C1 = f
    
End Sub

or

Range("C10:C13").FormulaR1C1 = Range("C3").FormulaR1C1

Upvotes: 4

Related Questions