Newbie: VBA problem when copying cell range

[Frank]

When I try to copy a horizontal cell range to the rows below, the cell
references in my formulas is incremented by two instead of one, as explained
in the example below.

Given this formulas C1=A1+B1, and D1=A1-B1

Using the formula below, the C2 formula then becomes A2+B2 as expected, while
C3 formula becomes A4+B4 (D3=A4-B4) and not A3+B3,
C4 formula becomes A6+B6 (D4=A6-B6) and not A4+B4 and so on.

With Worksheets("Sheet1")
..Range(.Cells(2, 3), .Cells(8, 5)).FormulaR1C1 = .Range(.Cells(1, 3),
..Cells(1, 5)).FormulaR1C1
End With

Please explain me why this happen, and how the formula should be corrected!

Regards

Frank Krogh

 

[Tom Ogilvy]

do it this way and you will get what you want:

Sub ABC()
With Worksheets("Sheet1")
..Range(.Cells(1, 3), .Cells(8, 5)) _
.Formula = .Range(.Cells(1, 3), _
..Cells(1, 5)).Formula
End With

End Sub

Note that I removed the R1C1 and I reassigned the formula to the original
cells (included them on the left side as well).

Use autofill if you don't want to have to think about it.