vba copy formula to multiple cells

Question

I know the ways of using ".PasteSpecial xlPasteFormulas" or "range.AutoFill", but I try to find a way to use the array variable.

I want to copy the formulas in range C4:D4 to the range C7:D11 which has multiple range.

C4 = A4+B4
D4 = Average(A4:C4)

So I made a vba script like this.

Sub test()
Dim  v
v = Range("C4:D4").FormulaR1C1
Range("C7:D11").FormulaR1C1 = v
End Sub

After running the vba, the formulas in c7 and d7 were like this, as I expected.

c7 = a7 + b7
d7 = average(A7:C7)

but the other cells' formulas were strange

c8 = a9 + b9
d8 = average(A9:C9)
c9 = a11 + b11
d9 = average(A11:C11)

and so on.....

My questions are: 1. Why is this happening? 2. Any suggestion about the way of copying some formulas to multiple range by the way of using the array variable?

Thank you in advance.

Axel Richter · Accepted Answer · 2019-01-01 08:03:40Z

1

Seems you have found a bug in Excel's VBA. On other hand if we are reading Range.FormulaR1C1 property (Excel) in nit-picking mode then in

Setting the formula of a multiple-cell range fills all cells in the range with the formula.

the "the formula" could also be read as "one formula". There is never told that we can set multiple formulas of a multiple-cell range.

So the only way seems to be doing this in two parts:

Sub test()
 Dim v As Variant
 v = Range("C4").FormulaR1C1
 Range("C7:C11").FormulaR1C1 = v
 v = Range("D4").FormulaR1C1
 Range("D7:D11").FormulaR1C1 = v
End Sub

This should still be faster than using copy/paste via clipboard or setting the formulas using loops.

edited Jan 1, 2019 at 8:03

answered Jan 1, 2019 at 7:05

Axel Richter

62.5k7 gold badges70 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Lord Elrond · Accepted Answer · 2019-01-01 21:59:07Z

1

It seems that excel is incrementing the formula's selected range by 1 and starting from 0 in C7:D7, which is why those show up correctly.

I think you can fix it by doing this:

for x = 7 To 11 
    Cells(x, 3).FormulaR1C1 = Range("C4").FormulaR1C1
    Cells(x, 4).FormulaR1C1 = Range("D4").FormulaR1C1
next x

If this doesn't work you can try writing the formulas in the vba code.

example:

for x = 7 To 11 
Cells(x, 3).value = Cells(x, 2).value + Cells(x, 1).value
Cells(x, 4).value = (Cells(x, 3).value + Cells(x, 2).value + Cells(x, 1).value)/3
next x

answered Jan 1, 2019 at 21:59

Lord Elrond

16.5k8 gold badges54 silver badges92 bronze badges

Collectives™ on Stack Overflow

vba copy formula to multiple cells

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related