PDA

View Full Version : Autofill or something else to multiply a formula

11-06-2012, 03:10 AM
Hello,

I am facing a problem and I can't find a solution.
I have a range with some "SUM" formulas in cells...let's say, for example, that in cell H1 I have the formula A1+C1+E1, in cell H2 I have A2+C2+E2 and so on until H10=A10+C10+E10.
Now, if I want to autofill the formula in cells J1:J10, the formulas will be J1=C1+E1+G1 ..... J10=C10+E10+G10. Practically, it jumps 2 columns - from A to C, from C to E and from E to G - and this is quite normal, but if I want to autofill the formula 2 columns to the right but in the way of jumping only one column in SUM? This means that the sums should be J1=B1+D1+F1 ... J10=B10+D10+F10.

Hope I was clear and I thank you for your kind support,

11-06-2012, 04:13 AM
I attached also a sample file.
Thank you!

Teeroy
11-06-2012, 09:32 PM
You could copy H1 to I1, drag it to J1 then autofill down, or try the following which does exactly that by VBA.

Sub FormulaEntry()
Dim LR As Long
Dim Temp As String
LR = Range("H" & Rows.Count).End(xlUp).Row
Temp = Range("I1").Formula
Range("I1").Formula = Range("H1").Formula
Range("J1").Resize(LR, 1).FormulaR1C1 = Range("I1").FormulaR1C1
Range("I1").Formula = Temp
End Sub