copying a formula to another cell

[Russ]

This is a trivial question but I have run into a wall trying to find the
answer.
I have a formula in a cell which I want to copy to the cells below with a
simple routine. A sample routine looks like this:

Public Sub test_case()
Dim r As Long
With ActiveWorkbook.Sheets(1)
For r = 2 To 5
.Cells(r, 1).Formula = .Cells(1, 1)
Next
End With
End Sub

In cell(1,1) is the sample formula =RC2/RC3. The routine, as it is, copies
the formula down to the 4 cells below. =RC2/RC3 shows in the cells but it is
not a formula that equates to the value. How do I change the code to get it
to be a real formula? Any help will be greatly appreciated

 

[CurlyDave]

Perhaps this would be the way to go

Dim r As Long
Dim s As String
s = "=RC2/RC3 "
Cells(1, 1) = s
With ActiveWorkbook.Sheets(1)
For r = 2 To 5
.Cells(r, 1).Formula = s
Next
End With

 

[JLGWhiz]

You were not too far off.

Public Sub test_case()
Dim r As Long
With ActiveWorkbook.Sheets(1)
For r = 2 To 5
.Cells(r, 1).Formula = .Cells(1, 1).Formula
Next
End With
End Sub

However, It copies the formula as absolute reference. i.e. If your formula
is =b1+c1, then each cell in the range will be =b1+c1 and not =b2+c2, =b3+c3,
etc.

 

[JLGWhiz]

I think this might be more what you are looking for.

Sub autFil()
Set SourceRange = Sheets(1).Range("A1")
Set FillRange = Sheets(1).Range("A1:A5")
Range("A1").Formula = "=b1/c1"
SourceRange.Autofill FillRange
End Sub

 

[OssieMac]

Hi Russ,

If you want the formula that is copied to be relative in the other cells
then you need to copy the cell not make the others equal to the original cell.

Public Sub test_case()
'Creates formulas and makes them relative
Dim r As Long
With ActiveWorkbook.Sheets(1)
For r = 2 To 5
.Cells(1, 1).Copy .Cells(r, 1)
Next
End With
End Sub


If you want the exact same formula in the other rows then this way.
Public Sub test_case2()
'Copies the exact same formula to other cells
Dim r As Long
With ActiveWorkbook.Sheets(1)
For r = 2 To 5
.Cells(r, 1) = .Cells(1, 1).Formula
Next
End With
End Sub

 

[Russ]

Thanks to all of you. I now have the solution.